如何使用group by，distinct和sum？

Question

I have 1 table: temp_shoppingcart .

In the temp_shoppingcart, i have temp_shoppingcart_id, user_id, product_id, quantity and color. Anytime, a user add an item in the shopping cart, it will add a new row to store the data.

So the data will have something like user_id: 1, product_id: 123, quantity: 1, color: black. If the user add the same item again, it will have the same data entered as a new row. So there will be the same row twice.

How do I display the data such that the same product will be group together as display as the same row but the quantity is the sum of 2 rows?

Answer 1

That's a pretty basic query concept actually. Maybe you can look at some MySQL tutorial and learn a bit more. Something like this: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Anyway the query below

SELECT product_id,SUM(quantity),GROUP_CONCAT(color)
FROM table
GROUP BY product_id

would display a row with the sum qty. The GROUP_CONCAT(color) will display for example, "red,red" if both entries "color" field are red.

I would suggest the query below if you want to group items by colors too.

SELECT user_id,product_id,color,SUM(quantity)
FROM temp_shoppingcart
GROUP BY user_id, product_id,color

If you don't want to group by colors

SELECT user_id,product_id,GROUP_CONCAT(color),SUM(quantity)
FROM temp_shoppingcart
GROUP BY user_id, product_id