Note: this is different from questions that ask "I want a count for each distinct row in a table" which has been answered numerous times. This is a filtered count, so the counting part of the query needs a more complex WHERE clause. Consider this dataset:
customer_id | user_id | age
-----------------------------
1 | 932 | 20
1 | 21 | 3
1 | 2334 | 32
2 | 232 | 10
2 | 238 | 28
3 | 838 | 39
3 | 928 | 83
4 | 842 | 12
I want to query this table and know the number of users over the age of 13 for each distinct customer_id. So the result would be:
customer_id | over_13_count
-----------------------------
1 | 2
2 | 1
3 | 2
4 | 0
I've tried something like this but it just runs forever, so I think I'm doing it wrong:
SELECT DISTINCT customer_id,
(SELECT COUNT(*) FROM mytable AS m2 WHERE m2.customer_id = m1.customer_id AND age > 13) AS over_13_count
FROM mytable AS m1
ORDER BY customer_id
Just use conditional aggregation:
SELECT customer_id,
SUM(CASE WHEN age > 13 THEN 1 ELSE 0 END) asover_13_count
FROM mytable m1
GROUP BY customer_id