CODEWITHSUNDEEP
mysql
Pr0no
Pr0no

Reputation: 4099

How to count the number of times a user_id is in a table?

Consider the following MySQL table:

user_id
-------
1
1
2
3
3
3
3

I would like an output of:

user_id  cnt
-------------
1        2
2        1
3        4

I though this would make that happen, but it doesn't:

SELECT user_id, COUNT(1) FROM table GROUP BY user_id

What am I doing wrong?

Upvotes: 1

Views: 92

Answers (3)

Lion
Lion

Reputation: 19027

SELECT user_id, count(user_id) as total FROM temp group by user_id;

The query produced this.

user_id  total
-------------
1        2
2        1
3        4

Upvotes: 1

Ellipsis
Ellipsis

Reputation: 117

Try this:

SELECT user_id, COUNT(user_id) as cnt FROM table GROUP BY user_id

Upvotes: 1

Bohemian
Bohemian

Reputation: 425003

SELECT user_id, COUNT(*) FROM table GROUP BY user_id;

btw, there is also the curiosity:

SELECT user_id, SUM(1) FROM table GROUP BY user_id

Both will give you the output you want.

Upvotes: 3

Related Questions