SQL query with GROUPING SETS to get subtotals with a HAVING clause

Question

Let's say I have a (fictional) table City with the following columns:

Country: string  
State: string 
City: string 
Population: integer

I want a query that shows the total population by State, by Country and overall, excluding states below 1,000,000 inhabitants.

SELECT Country, State, SUM(Population) AS [Total]
FROM   City
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () )     
HAVING SUM(Population) < 1000000
ORDER  BY Country, State

The problem with this query is that the Country and Overall totals will include the population of states below 1,000,000 inhabitants (meaning the total of states doesn't match the total of its parent country). (This is assuming that the countries have over 1,000,000 inhabitants)

How can we amend the query above to fulfill the requirements?

I'm using sql azure...

Answer 1

You have to apply the "having clause" before grouping set

SELECT Country, State, SUM(Population) AS [Total]
FROM
(
    SELECT Country, State, SUM(Population) AS [Population]
    FROM   City
    GROUP  BY Country, State  
    HAVING SUM(Population) > 1000000
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () ) 
--HAVING SUM(Population) > 1000000
ORDER BY Country, State

here it's a sample :

;with City
AS
(SELECT 'Count1' as Country,'St1' as State,'C1' as City,100 As Population Union ALL
 SELECT 'Count1', 'St1' , 'C2', 50 Union ALL
 SELECT 'Count1','St2' , 'CCC 1', 50 Union ALL
 SELECT 'Count1','St2' , 'CCC 2 ', 40 
)


SELECT Country, State, SUM(Population) AS [Total]
FROM
(
    SELECT Country, State, SUM(Population) AS [Population]
    FROM   City
    GROUP  BY Country, State  
    HAVING SUM(Population) > 100
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () ) 
--HAVING SUM(Population) > 100
ORDER BY Country, State

output:

Country State   Total
                150
Count1          150
Count1  St1     150

dbfiddle here