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