CODEWITHSUNDEEP
presto
Arun Kumar Dave
Arun Kumar Dave

Reputation: 121

Presto SQL - distinct in selective group by

ID  Name     Gender  Country 
1   Arun     Male    India
2   Akshay   Male    England
3   Chethna  Female  India
4   Priya    Female  China
5   Piyush   Male    India
6   Arun     Male    England
7   Tisha    Female  England
8   Chethna  Female  China

I want to group them into male/female first, then the country associated.

Query1 : select Gender, count(distinct name) from Table group by Gender

Output: 
Gender   count(distinct name)
Male     3
Female   3

Copying the result in JSON like this, result : {male : {count : 3}, female : {count : 3} }

Query2 : select Gender, Country, count(distinct name) from Table group by Gender, Country

Output:
Gender  Country    count(distinct name)
Male    India      2
Male    England    2
Female  India      1
Female  China      2
Female  England    1

Adding this result in the above Json,

result : {Male:{count:3,India:{count:2},England:{count:2}},Female:{count:3,India:{count:1},China:{count:2},England:{count:1}}}

So can I achieve this in a single query?

Upvotes: 0

Views: 14638

Answers (1)

Martin Traverso
Martin Traverso

Reputation: 5316

You can compute the counts by gender and by gender+country in a single query by using GROUPING SETS:

WITH data(id, name, gender, country) AS (
   VALUES
   (1, 'Arun',    'Male'  , 'India'),
   (2, 'Akshay',  'Male'  , 'England'),
   (3, 'Chethna', 'Female', 'India'),
   (4, 'Priya',   'Female', 'China'),
   (5, 'Piyush',  'Male'  , 'India'),
   (6, 'Arun',    'Male'  , 'England'),
   (7, 'Tisha',   'Female', 'England'),
   (8, 'Chethna', 'Female', 'China'))

SELECT gender, country, count(distinct name)
FROM data
GROUP BY GROUPING SETS ((gender), (gender, country))

which produces:

 gender | country | _col2
--------+---------+-------
 Male   | England |     2
 Female | China   |     2
 Male   | NULL    |     3
 Female | NULL    |     3
 Female | India   |     1
 Male   | India   |     2
 Female | England |     1
(7 rows)

Upvotes: 2

Related Questions