'Select distinct and count number of rows
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
