SQL: How do I get a filtered count for each distinct row in a table?

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

Answer

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

Advertisement

Answer