3

I'm having some trouble with my shopping cart table. I've created a table that looks like this:

SQL Fiddle

My problem is that I want to be able to select the product id, and the count on how many times this product id is repeated in the table so I can display how many quantities of an item the user has in the cart.

Looking for an output result that looks like this:

| user_id | product_id | num of prodcuts|
-----------------------------------------
|   12    |     43     |        3       |
|   12    |     51     |        1       |
|   21    |     39     |        2       |
|   17    |     41     |        1       |

So I want to select product_id and also count it in a single query and I got no idea of how this is done so I'd really appreciate some help.

Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
Erik Åstrand
  • 369
  • 2
  • 5
  • 14
  • 1
    Several answers seem to be grouping by only product_id, so I added some more data to your example sqlfiddle where multiple users order the same product_id: http://sqlfiddle.com/#!2/a6828/1 – Digital Chris Mar 13 '14 at 15:32

4 Answers4

3
select user_id, product_id, count(product_id) as num_of_prod 
from cart
group by user_id, product_id

http://sqlfiddle.com/#!2/4f023/12

Digital Chris
  • 6,177
  • 1
  • 20
  • 29
1

Query :

SELECT user_id,product_id,count(*) AS amount
FROM cart
GROUP BY product_id;
Up_One
  • 5,213
  • 3
  • 33
  • 65
1

To select all products and count how many there are REGARDLESS of the cart_id and the user_id you can use:

SELECT *, COUNT(*) AS num_products FROM cart GROUP BY user_id, product_id;

If you need something more specific or tailored, let me know.

SparoHawk
  • 557
  • 2
  • 10
0
SELECT 
    user_id, 
    product_id, 
    count(*) AS 'num of prodcuts' 
FROM 
    cart 
GROUP BY 
    product_id, 
    user_id 
ORDER BY 
    user_id
hanleyhansen
  • 6,304
  • 8
  • 37
  • 73