Select distinct and count number of rows-CodePudding

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

CodePudding user response：

A simple count(*) should do the trick:

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

Fiddle