SQL Grouping and counting using existing row

Question

Lets say we have a table Requests with structure:

user_id 
product_id 
count

With entries like:

I want to count how much of each product each user has.

And get output like this:

Edit: removed Wow, that was easy! Thank you!!. There is no need to "thank", just "accept answer" and "up vote" it's the same. — Michał Powaga
– Michał Powaga, Commented Nov 16, 2011 at 14:13

Mark Byers · Accepted Answer · 2011-11-16 14:03:25Z

2

Use GROUP BY with the SUM aggregate function:

SELECT user_id, product_id, SUM(count) AS total
FROM Requests
GROUP BY user_id, product_id

answered Nov 16, 2011 at 14:03

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

WWW · Accepted Answer · 2011-11-16 14:02:33Z

2

Your query will look something like this:

SELECT user_id, product_id, SUM(`count`)
FROM Requests
GROUP BY user_id, product_id

I wouldn't name a field "count" if I had the choice, as it's a SQL function and could cause weird naming conflicts down the road.

answered Nov 16, 2011 at 14:02

WWW

9,8782 gold badges32 silver badges33 bronze badges

Gustav Bertram · Accepted Answer · 2011-11-16 14:36:23Z

0

You can find a tutorial on how to use GROUP BY here:

You can also find specific information on the syntax and use of GROUP BY in MySQL in the MySQL Manual:

answered Nov 16, 2011 at 14:36

Gustav Bertram

15k3 gold badges46 silver badges68 bronze badges