Grouping Sets, With Rollup and With Cube example

Grouping sets allow multiple grouping on columns in a single data set.
Rollup and Cube also used for summary rows (allows multiple grouping same as Grouping sets)

Example:

DECLARE @t TABLE
    (
      idx INT IDENTITY(1, 1) ,
      Country VARCHAR(50) ,
      City VARCHAR(50) ,
      NoOfJobOpenings INT
    );

INSERT  INTO @t
        ( Country ,
          City ,
          NoOfJobOpenings
        )
        SELECT  'Pakistain' ,
                'Karachi' ,
                50
        UNION
        SELECT  'Pakistain' ,
                'Lahore' ,
                25
        UNION
        SELECT  'Pakistain' ,
                'Islamabad' ,
                10
        UNION
        SELECT  'Pakistain' ,
                'Islamabad' ,
                05
        UNION
        SELECT  'India' ,
                'Mumbai' ,
                100
        UNION
        SELECT  'India' ,
                'Banglore' ,
                40
        UNION
        SELECT  'India' ,
                'Banglore' ,
                30
        UNION
        SELECT  'India' ,
                'Goa' ,
                20; 

--SELECT  *
--FROM    @t;



--** Grouping sets
SELECT  Country ,
        City ,
        SUM(NoOfJobOpenings) AS TotalNumberofOpenings
FROM    @t
GROUP BY GROUPING SETS(
                       ( Country, City ), ( Country ), ( ));

--*** With RollUP
SELECT  Country ,
        City ,
        SUM(NoOfJobOpenings) AS TotalNumberofOpenings
FROM    @t
GROUP BY Country ,
        City
        WITH ROLLUP;


--*** With Cube
SELECT  Country ,
        City ,
        SUM(NoOfJobOpenings) AS TotalNumberofOpenings
FROM    @t
GROUP BY Country ,
        City
        WITH CUBE;