jcm jcm - 3 months ago 7
SQL Question

How to get total unique users as well as grouping by category

I have the following SQL table:

user_id | device | num_uses
---------------------------
1 | phone | 10
1 | tv | 5
2 | phone | 15
3 | tv | 45


I want to come up with a query which will return the number of users per device, but also the total number of unique users.

Is it possible to do this with one query, or is my only option to create 2 queries, one for the number of users per device, then another to get the distinct number of users?

The query I have to get the number of users by device is:

select count(user_id), device from usage group by device;


which gives me:

count | device
---------------
2 | phone
2 | tv


Expected result would be:

category_count | device | total_distinct_users
------------------------------------------------
2 | phone | 3
2 | tv | 3

Answer

You want count(distinct). I think like this:

select device, sum(uses) as num_uses, count(*) as num_users,
       count(distinct user_id) as num_distinct_users
from usage
group by device;

EDIT:

If you want the number of users per device and the total number of unique users, then you might want:

select u.device, count(*) as num_users,
       uu.num_distinct_users
from usage u cross join
     (select count(distinct user_id) as num_distinct_users from usage) uu
group by device, uu.num_distinct_users;