Siddarth Siddarth - 3 months ago 16
SQL Question

get the closest

How do I get output in the below format using sql?
I am able to do it with case sum, but I am not able to get unique user count in the same line.

id unique_users male_count female_count
101 3 1 2
201 1 0 1
.
.

Answer

Alternative counts distinct males and females.

SELECT
  ID, 
  COUNT(DISTINCT "USER") AS unique_users,
  COUNT(DISTINCT DECODE(gender,'M',"USER")) AS male_count,
  COUNT(DISTINCT DECODE(gender,'F',"USER")) AS female_count
FROM your_table
GROUP BY ID
ORDER BY ID;

And the same but without the Oracle specific DECODE function.

SELECT
  ID, 
  COUNT(DISTINCT "USER") AS unique_users,
  COUNT(DISTINCT CASE gender WHEN 'M' THEN "USER" ELSE NULL END) AS male_count,
  COUNT(DISTINCT CASE gender WHEN 'F' THEN "USER" ELSE NULL END) AS female_count
FROM your_table
WHERE ID = 101
GROUP BY ID
ORDER BY ID;

I've quoted "USER" as it has a specific meaning in Oracle.