SQL query with GROUPING SETS to get subtotals with a HAVING clause
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) &gt; 1000000
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () ) 
--HAVING SUM(Population) &gt; 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) &gt; 100
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () ) 
--HAVING SUM(Population) &gt; 100
ORDER BY Country, State


output:

Country State   Total
                150
Count1          150
Count1  St1     150


dbfiddle here