1

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...

2

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

  • Thanks. I was hoping to achieve it with a single group by but I don't think this is possible. – Clement Oct 26 '18 at 22:46
  • I posted a variant of this question that is more complex because it is using a non additive measure. dba.stackexchange.com/questions/222799/… – Clement Nov 18 '18 at 1:18
  • @Clement, see here a try of this request dbfiddle.uk/… – Sabin Bio Nov 18 '18 at 6:35
  • thank you so much @sabin-bio. Feel free to propose as an answer on the other question, I will mark it as accepted since it is more generic that the current answer. In particular your solution support grouping by multiple colums. – Clement Nov 19 '18 at 0:25
  • @Clement, ok , I posted it – Sabin Bio Nov 19 '18 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.