Having difficulties converting SQL query to Laravel builder

Question

I am trying to convert the following query to laravels using query builder but having some issue with group by clause and sum aggregated functions

SELECT code, SUM(quantity) as quantity, SUM(sale_price) as price
FROM `orders` o
GROUP By code

I have tried this, this is what I have after so many desperate tries.

$args['orders'] = DB::table('orders')
->havingRaw('sum(sale_price)')
->havingRaw('code')
->where([ 'shop_id' => $shop_id ])
->groupBy('code')
->get();

EddyTheDove · Accepted Answer · 2017-02-28 12:06:08Z

1

Try with

$args['orders'] = DB::table('orders')
->where('shop_id', '=', $shop_id)
->select('code', DB::raw('sum(quantity) as quantity, sum(sale_price) as price'))
->groupBy('code')
->get();

Order by

$args['orders'] = DB::table('orders')
->where('shop_id', '=', $shop_id)
->select('code', DB::raw('sum(quantity) as quantity, sum(sale_price) as price'))
->groupBy('code')
->orderBy('quantity')
->get();

Or try

->orderByRaw('sum(quantity)')

edited Feb 28, 2017 at 12:06

answered Feb 28, 2017 at 11:54

EddyTheDove

13.2k2 gold badges40 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Waleed Over a year ago

How would I add order_by column in this?

Waleed Over a year ago

Nah, order_by is actually a column which will be joined to another table.

EddyTheDove Over a year ago

Then simply use tablename.column inside of orderBy()

Waleed Over a year ago

SELECT code, order_by, SUM(quantity) as quantity, SUM(sale_price) as price FROM orders o GROUP By code

EddyTheDove Over a year ago

Then simply add ->orderBy('order_by') to the query before get()

|

Collectives™ on Stack Overflow

Having difficulties converting SQL query to Laravel builder

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related