CODEWITHSUNDEEP
phplaraveleloquentquery-builderlaravel-query-builder
Adam Lambert
Adam Lambert

Reputation: 1431

Laravel Query Builder - Sum Where and Sum Where

I have a table called stock_movements:

| product_id | type | qty  |
|------------|------|------|
| 1          | A    | 2    |
| 1          | A    | 1    |
| 1          | A    | 7    |
| 1          | B    | -2   |
| 1          | B    | -4   |
| 1          | B    | -1   |
| 2          | A    | 2    | 
| 2          | A    | 1    |
| 2          | A    | 7    | 
| 2          | B    | -3   | 
| 2          | B    | -3   |
| 2          | B    | -1   |

I am trying to create a collection of the products where which have the values of the sum of A and sum of B.

i.e. Group by products, type Sum qty for each type

The key thing is that it is a collection against the product, which is the bit I am struggling with. Does anyone have any advice?

So far I have got...

    return $this->stockMovements()->groupBy('product_id')
        ->selectRaw('sum(qty), product_id')
        ->where('type', $this->type)
        ->get();

But this does not split out for types A and B.

Upvotes: 1

Views: 8297

Answers (2)

Amitesh Bharti
Amitesh Bharti

Reputation: 15775

Using A Raw Expression

  $result = DB::table('stock_movements')
                ->select(DB::raw('sum(qty) as sum_qty, type'))
                ->groupBy('type')
                ->get();

Rule of thumb for the use of aggregation functions in the SELECT clause.

If a select block does have a GROUP BY clause, any column specification specified in the SELECT clause must exclusively occur as a parameter of an aggregated function or in the list of columns given in the GROUP BY clause, or in both.

For more details : http://www.informit.com/articles/article.aspx?p=664143&seqNum=6

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

You need a basic pivot query, which would look something like this in Laravel:

return $this->stockMovements()
        ->selectRaw("SUM(CASE WHEN type = 'A' THEN qty ELSE 0 END) AS sum_a, ".
                    "SUM(CASE WHEN type = 'B' THEN qty ELSE 0 END) AS sum_b, product_id")
        ->groupBy('product_id')
        ->get();

I removed the WHERE clause, because your data only seems to have two types, and there isn't much sense in restricting that. If you really want the sum of quantity for just A or B, then we can write a much simpler Laravel/MySQL query.

Upvotes: 4

Related Questions