Joining to the same table with different filters-CodePudding

I am trying to count, for each id, the number of distinct app downloaded by the user, grouping the count by category. An example of the separate queries are as such:

SELECT id
,COUNT(DISTINCT app) AS gaming_apps
FROM apps_table 
WHERE app IN ('Clash of Clans', 'Valorant', 'PUBG') 
GROUP BY id 

SELECT id 
,COUNT(DISTINCT app) AS msg_apps
FROM apps_table 
WHERE app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger') 
GROUP BY id 

SELECT id 
,COUNT(DISTINCT app) AS fin_apps
FROM apps_table 
WHERE app IN ('Yahoo Finance', 'Robinhood') 
GROUP BY id

Ideally, I would want to return a table with the columns id gaming_apps msg_apps fin_apps. I thought about LEFT JOIN the 3 queries together but I am not sure how, and wrapping each as a subquery would be too unwieldy.

I also tried this, to no avail because a new row is inserted per download or app update:

SELECT id
,COUNT(CASE WHEN app IN ('Clash of Clans', 'Valorant', 'PUBG') THEN 1 ELSE NULL END) AS gaming_apps
,COUNT(CASE WHEN app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger')  THEN 1 ELSE NULL END) AS msg_apps
,COUNT(CASE WHEN app IN ('Yahoo Finance', 'Robinhood') THEN 1 ELSE NULL END) AS fin_apps 
FROM apps_table 
GROUP BY id

CodePudding user response：

You are close, you can use SUM instead of count in your query.

SELECT id
,SUM(CASE WHEN app IN ('Clash of Clans', 'Valorant', 'PUBG') THEN 1 ELSE 0 END) AS gaming_apps
,SUM(CASE WHEN app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger')  THEN 1 ELSE 0 END) AS msg_apps
,SUM(CASE WHEN app IN ('Yahoo Finance', 'Robinhood') THEN 1 ELSE 0 END) AS fin_apps 
FROM apps_table 
GROUP BY id

I think this should work. if it doesnt work then, like you said do a left join.

SELECT main.id as id,
gaming_apps,
msg_apps, 
...

FROM apps_table  main
LEFT OUTER JOIN (
SELECT id,COUNT(DISTINCT app) AS gaming_apps FROM apps_table  WHERE app IN ('Clash of Clans', 'Valorant', 'PUBG')  GROUP BY id) gaming_apps ON gaming_apps .id=main.id
LEFT OUTER JOIN (
SELECT id ,COUNT(DISTINCT app) AS msg_apps FROM apps_table  WHERE app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger')  GROUP BY id) msg_apps ON msg_apps.id=main.id 
...

CodePudding user response：

If you want distinct counts, then use count(distinct):

SELECT id,
       COUNT(DISTINCT CASE WHEN app IN ('Clash of Clans', 'Valorant', 'PUBG') THEN app END) AS gaming_apps,
       COUNT(DISTINCT CASE WHEN app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger') THEN app END) AS msg_apps,
       COUNT(DISTINCT CASE WHEN app IN ('Yahoo Finance', 'Robinhood') THEN app END) AS fin_apps 
FROM apps_table 
GROUP BY id ;

Note that ELSE NULL is redundant, because NULL is the default value if there are no matches in the CASE expression.