CODEWITHSUNDEEP
sqlsql-server
Kerberos
Kerberos

Reputation: 1266

Using HAVING with GROUPING SETS

My sql string as below

SELECT Country, City, COUNT(*) AS [Count]
FROM   CountriesAndCities
GROUP  BY GROUPING SETS ( ( Country, City ), ( Country) )     
ORDER  BY Country, City

I am getting results as below 

Country     City        CountryCount
----------  ----------  ------------
France      NULL        4 
France      Paris       4           
Spain       NULL        6 
Spain       Barcelona   3           
Spain       Madrid      3

If country has got only one city record, can I get results as below with using HAVING

Country     City        CountryCount
----------  ----------  ------------
France      Paris       4           
Spain       NULL        6 
Spain       Barcelona   3           
Spain       Madrid      3

Upvotes: 1

Views: 1741

Answers (1)

MatBailie
MatBailie

Reputation: 86706

SELECT Country, City, COUNT(*) AS [Count]
FROM   CountriesAndCities
GROUP  BY GROUPING SETS ( ( Country, City ), ( Country) )
HAVING GROUPING(City) = 0
    OR COUNT(DISTINCT City) > 1
ORDER  BY Country, City
  • GROUPING(City) = 0 if City one of the fields currently being grouped by.
  • Conversely, when GROUPING(City) = 1 then City is reported as NULL.

This means that we always include the rows where the City is mentioned (not NULL).

For the other rows, where GROUPING(City) = 1 aka City IS NULL, then we only include the row if more than one City has been aggregated in to the result.

Upvotes: 7

Related Questions