Select distinct and count number of rows

Question

I have a table with this structure

+----------+----------+
| user_id  | tema_id  |
+----------+----------+
|        1 |    1     |
|        2 |    1     |
|        3 |    2     |
|        4 |    3     |
|        5 |    2     |
|        6 |    3     |
|        7 |    1     |
+----------+----------+

What I want to get in only one query is the total of different tema_id by tema_id. I have this query but it returns the different tema_id but the count column to one instead of the total of that tema_id.

SELECT tema_id, COUNT(DISTINCT(tema_id)) as total
FROM push_subscriptions
GROUP BY tema_id

Return this:

+----------+----------+
| tema_id  | total    |
+----------+----------+
|        1 |    1     | -> must be 3
|        2 |    1     | -> must be 2
|        3 |    1     | -> must be 2
+----------+----------+

Thank you

Replace COUNT(DISTINCT(tema_id)) to COUNT(tema_id).

Arpit Mittal
– Arpit Mittal

2022-03-05 18:05:26 +00:00
Commented Mar 5, 2022 at 18:05 — Arpit Mittal
– Arpit Mittal, Commented Mar 5, 2022 at 18:05

Zakaria · Accepted Answer · 2022-03-05 18:04:09Z

2

A simple count(*) should do the trick:

select tema_id, count(*) as total
from push_subscriptions
group by tema_id;

Fiddle

answered Mar 5, 2022 at 18:04

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Select distinct and count number of rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related