GeoVIP GeoVIP - 1 year ago 79
SQL Question

How to separate group by if value exists in multiple categories

I have a table like this:

CREATE TEMP TABLE users_category
user_id int,
category_id int

insert into users_category (user_id, category_id)
values (100, 1), (100, 2), (103, 4), (105, 4), (106, 2), (107, 1)

Then I'm trying to calculate how much users use categories like :

select category_id,count(*)
from users_category
group by category_id

Response is :

category_id count
4 2
1 2
2 2

How to add logic if users exists in more than one category , calculate it in other category.

For example user 100 exists in category 1 and 2 and i will not calculate it for both category-s . I want add new category ex. 99 and must add users there.

Response must be like :

category_id count
4 2
1 1
2 1
99 1 (user who was in both category)

How to do it ?

Answer Source

You need to aggregate first at the user level and then at the category level:

select category_id, count(*)
from (select user_id,
             (case when min(category_id) = max(category_id) then min(category_id)
                   else 99
              end) as category_id
      from users_category uc
      group by user_id
     ) uc
group by category_id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download