MySQL GROUP BY and SELECT GROUP BY

Question

This are my tables

Products: enter image description here

Orders

enter image description here

This is my query:

SELECT DISTINCT
    orders.*, 
    IF (orders.price_type = 1, products.price * orders.quantity, products.discount_price * orders.quantity) AS subtotal
FROM
    orders
LEFT JOIN
    products
ON
    orders.product_id = products.id
GROUP BY 
    order_id

Result:

enter image description here

If you notice subtotal, it is only calculated depends on selected rows. How I can add those result of other rows with the same order_id?

score 1 · Accepted Answer · answered Dec 07 '13 at 01:11

1

I havent' tested this, but I think this is what you are looking for.

SELECT DISTINCT
    orders.*, 
    sum(IF (orders.price_type = 1, products.price * orders.quantity, products.discount_price * orders.quantity)) AS subtotal
FROM
    orders
LEFT JOIN
    products
ON
    orders.product_id = products.id
GROUP BY 
    order_id

answered Dec 07 '13 at 01:11

Brian Hoover

7,861
2
28
41

I'm not sure about this solution. It still returns the same number of rows, but it will surely give the total for each order_id, but with the rest of the columns related to only one of the order lines. But if it is accepted, who am i to say otherwise – Filipe Silva Dec 07 '13 at 01:45

score 0 · Answer 2 · answered Dec 07 '13 at 01:23

Since you say you want the subtotal, i assume that you want only to calculate the value for each line of the orders table. To get that you have to remove the DISTINCT and the GROUP BY. (DISTINCT and GROUP BY are a little weird done together for something like this, and if you want every row to be returned with the subtotal, you don't need them):

SELECT orders.*, 
    IF(orders.price_type = 1,
        products.price * orders.quantity, 
        products.discount_price * orders.quantity) AS subtotal
FROM orders
LEFT JOIN products ON orders.product_id = products.id

This will get you the subtotal for every line in the orders table.

If you want the results GROUPED BY order_id, you can't really do SELECT * since the GROUP BY will make wrong assumptions about the other columns and you end up with wrong results, like you experienced. you can do:

SELECT orders.order_id,
       orders.order_date, 
       SUM(IF(orders.price_type = 1,
            products.price * orders.quantity, 
            products.discount_price * orders.quantity)) AS subtotal
FROM orders
LEFT JOIN products ON orders.product_id = products.id
GROUP BY orders.order_id, orders.order_date

MySQL GROUP BY and SELECT GROUP BY

2 Answers2