SQL SERVER – Introduction to Rollup Clause

Create a Database
Then new Script
Then Fire this.
------------------------------------------------------------------------------

CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT)
GO


INSERT INTO tblPopulation VALUES('India''Delhi','East Delhi',)
INSERT INTO tblPopulation VALUES('India''Delhi','South Delhi',)
INSERT INTO tblPopulation VALUES('India''Delhi','North Delhi',5.5)
INSERT INTO tblPopulation VALUES('India''Delhi','West Delhi',7.5)
INSERT INTO tblPopulation VALUES('India','Karnataka','Bangalore',9.5)
INSERT INTO tblPopulation VALUES('India''Karnataka','Belur',2.5)
INSERT INTO tblPopulation VALUES('India''Karnataka','Manipal',1.5)
INSERT INTO tblPopulation VALUES('India''Maharastra','Mumbai',30)
INSERT INTO tblPopulation VALUES('India''Maharastra','Pune',20)
INSERT INTO tblPopulation VALUES('India''Maharastra','Nagpur',11 )
INSERT INTO tblPopulation VALUES('India''Maharastra','Nashik',6.5)
GO



---To Display 


SELECT 
ISNULL(Country,'All Country') AS Country,
ISNULL([State],'All State') AS State,
ISNULL(City,'All City') AS City,SUM ([Population (in Millions)]AS [Population (in Millions)]FROM tblPopulationGROUP BY Country,[State],City WITH ROLLUP







Details: http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause