CODEWITHSUNDEEP
mysqlsql
Francesco Jo
Francesco Jo

Reputation: 63

MySQL - How can I get counts of rows in two columns, by single query?

I have following data:

| user_id | item_id |
|---------+---------|
|    3    |     2   |
|    3    |    11   |
|    4    |     1   |
|    4    |     2   |
|    4    |    55   |
|    4    |     3   |
|    5    |     9   |

where item_id is distinct in same user_id but it could be duplicated(see user_id = 3 and user_id = 4 have both item_id = 2). What I want to do is, aggregate these columns as "number of rows of item_id per user_id) which will be look like:

| user_id | count(`item_id`) |
|---------+------------------|
|    3    |         2        |
|    4    |         4        |
|    5    |         1        |

Currently I'm naïvely doing this by external logic which performs SELECT COUNT(item_id) WHERE user_id='${user_id}' on every user_ids. As you expected, this will getting worse when user_id is increased.

How can I write a single SQL query to achieve this? Please help me. Thanks in advance.

Upvotes: 0

Views: 79

Answers (2)

Manish Chauhan
Manish Chauhan

Reputation: 570

You can use count and GROUP BY clause to get desired result

E.g

SELECT user_id, count(item_id)
FROM table_name
GROUP BY user_id

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try below with count aggregation: when you use group by with user_id, it will give you user id wise count

select user_id, count(item_id)
from tablename
group by user_id

Upvotes: 1

Related Questions