CODEWITHSUNDEEP
sql-servert-sqlrollup
Royi Namir
Royi Namir

Reputation: 148664

SQL group by with Rollup misunderstanding?

Ive read here about it ( use it rarely)

but I have a problem understanding something :

this is the datasource :

enter image description here

according to this query : 

SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country,[State],City WITH ROLLUP

if im grouping by : Country, State, City

so every row is distinct ! ( because of the city)

so after every row - there should be a "middle sum "

but there isn't.

what am I missing ?

p.s. the result is :

enter image description here

Upvotes: 2

Views: 329

Answers (1)

usr
usr

Reputation: 171216

The last column never gets a "middle sum" even if it is not distinct. Lets say all your cities were either "A" or "B". You wouldn't want a "middle sum" per city because you already have it! You have the sums for "A" and "B" already. Also, you couldn't distinguish the "middle sums" for "A" and "B" because they were both set to null. The two rows would be identical (with different counts).

Upvotes: 1

Related Questions