CODEWITHSUNDEEP
phpmysqllaraveleloquent
Marcus Christiansen
Marcus Christiansen

Reputation: 3197

Laravel groupBy column and add totals based on Quantity column

I have a pivot table in Laravel (order_product) which records each product which belongs to an order.

I use the following columns "order_id, product_id, qty"

If I use 

DB::table('order_product')->selectRaw('*, count(*)')->groupBy('product_id');

I get the count of each product occurrence.

Some products, however, occur more than once in an order. How do I multiply each product by the qty column and then group by product_id and count them together?

At the end I need to get the total count of all orders grouped by product.

EDIT Here's my database table

enter image description here

Desired output:

Product (ID = 30) occurs 5 times in the table, from 5 different orders. The product total multiplied by the quantity columns would equal to 13

Upvotes: 0

Views: 2003

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

I think you want to aggregate using the sum of the quantity field, for each product. Hence, the following should work:

DB::table('order_product')->selectRaw('product_id, SUM(qty)')->groupBy('product_id');

This would correspond to the following raw MySQL query:

SELECT
    product_id,
    SUM(qty)
FROM order_product
GROUP BY
    product_id

Upvotes: 2

Related Questions