qin.sun qin.sun - 4 years ago 140
SQL Question

How to select count(distinct) with conditon inside groups

The SQL I want is something like this:

SELECT week_no, type,
SELECT count(distinct user_id) FROM group WHERE pts > 0
FROM base_table
GROUP BY week_no, type


But I know I can use count(distinct user_id) to count the total number of users in each groups but how can I count the number of users who meet the condition 'pts > 0'?

I'm using mysql,

Thanks.

Answer Source

You probably want conditional aggregation:

SELECT week_no, type, COUNT(DISTINCT CASE WHEN pts > 0 THEN user_id END)
FROM base_table
GROUP BY week_no, type;

SqlFiddleDemo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download