Count Group By and Separate If Included in Both Group

Question

Not sure if this question is duplicated yet or not.

I have a simplified table below

User	Interest
Jason	Art
Jason	Sport
Sam	Sport
Sam	Art
Steve	Sport
Desmond	Sport
Tania	Art

Here's the result that I want to achieve

Interest	Count
Art	2
Sport	2
Both	2

I Managed to make a subquery to achieve the value for the Both data by this query

SELECT COUNT(USER) FROM (
SELECT User, COUNT(DISTINCT Interest) as interest_type FROM table WHERE interest_type = 2)

But for the user that are exclusively have Interest in Art and in Sport it's not separated.

Answer 1

You could use conditional aggregation here:

WITH cte AS (
    SELECT User,
           CASE WHEN COUNT(CASE WHEN Interest = 'Art' THEN 1 END) > 0 AND
                     COUNT(CASE WHEN Interest = 'Sport' THEN 1 END) > 0
                THEN 'Both'
                WHEN COUNT(CASE WHEN Interest = 'Art' THEN 1 END) > 0
                THEN 'Art'
                ELSE 'Sport' END AS Interest
    FROM yourTable
    GROUP BY User
)

SELECT Interest, COUNT(*) AS Count
FROM cte
GROUP BY Interest;

On MySQL or BigQuery, we can shorten the above to:

WITH cte AS (
    SELECT User,
           CASE WHEN SUM(Interest = 'Art') > 0 AND SUM (Interest = 'Sport') > 0
                THEN 'Both'
                WHEN SUM(Interest = 'Art') > 0
                THEN 'Art'
                ELSE 'Sport' END AS Interest
    FROM yourTable
    GROUP BY User
)

SELECT Interest, COUNT(*) AS Count
FROM cte
GROUP BY Interest;

Answer 2

Assuming your database supports the over() clause:

select
      case when num_interests = 1 then interest else 'both' end as interest
    , count(distinct user) as "Count"
from (
    select
      interest
    , user
    , count(*) over(partition by user) as num_interests
    from yourTable
    ) d
group by
      case when num_interests = 1 then interest else 'both' end