Set of groups by varying columns is called Grouping Sets. Grouping sets are equivalent to a Union All of quires.Microsoft introduce Grouping Sets in MS SQL 2008, it is introduce for developer to work easily.
you can also use Union All if you not have MS SQL 2008 or later versions.

Syntax

Group by Grouping sets  (col1,col2,col3,.....,coln)

Let's Start. Open your MS SQL server 2008. Create a new database or select existing database where you want to work. In this Example, i am using existing database.I have created temporary virtual tables with declare statement. A temporary virtual table is a table which look like a table but not store information in a database. Virtual table are read only.Virtual table are destroy automatically when the query execution is stop. Virtual table can not use syntax Drop.

Create new temporary virtual tables  named Patient and PatientVisits  as shown in Figure 1.1

Figure 1.1

DECLARE @PATIENT TABLE
    (
      ID BIGINT ,
      FULLNAME NVARCHAR(MAX) ,
      DOB DATE ,
      GENDER CHAR(1)
    )
DECLARE @PATIENTVISITS TABLE
    (
      PATIENT_ID BIGINT ,
      VISIT_DATE DATE ,
      VISIT_TIME TIME ,
      DOCTOR_FEE MONEY
    )

Let's store some records in Patient and PatientVisits temporary virtual tables as  shown in Figure 1.2

Figure 1.2

INSERT  INTO @PATIENT
        ( ID, FULLNAME, DOB, GENDER )
VALUES  ( 1, 'AAMIR HASAN', '08/13/1985', 'M' ),
        ( 2, 'AWAIS AHMED', '01/05/1965', 'M' ),
        ( 3, 'HASAN', '02/23/1980', 'M' ),
        ( 4, 'MAYRA GIBRAM', '08/13/1975', 'F' ),
        ( 5, 'SABA KHAN', '02/13/1995', 'F' ),
        ( 6, 'AAMIR HASAN', '01/01/1985', 'M' ),
        ( 7, 'MAHWISH HASAN', '04/17/1988', 'F' ),
        ( 8, 'SANA KHAN', '01/11/1981', 'F' ),
        ( 9, 'ZAHOOR AHMED', '08/13/1949', 'M' )
     
     
INSERT  INTO @PATIENTVISITS
        ( PATIENT_ID, VISIT_DATE, VISIT_TIME, DOCTOR_FEE )
VALUES  ( 1, '12/12/1981', '12:09:09', 500 ),
        ( 2, '01/12/1982', '12:09:09', 400 ),
        ( 3, '11/12/1982', '12:09:09', 300 ),
        ( 4, '11/12/1982', '12:09:09', 500 ),
        ( 2, '11/12/1982', '12:09:09', 800 ),
        ( 5, '11/11/2000', '12:09:09', 900 ),
        ( 6, '11/11/2003', '12:09:09', 500 ),
        ( 2, '11/11/2003', '12:09:09', 100 ),
        ( 3, '11/11/2003', '12:09:09', 500 ),
        ( 6, '11/12/2003', '12:09:09', 500 ),
        ( 7, '11/12/2003', '12:09:09', 500 ),
        ( 8, '11/12/2004', '12:09:09', 500 ),
        ( 1, '01/09/2005', '12:09:09', 500 ),
        ( 2, '05/09/2006', '12:09:09', 1500 ),
        ( 9, '05/09/2007', '12:09:09', 900 ),
        ( 1, '05/09/2008', '12:09:09', 100 ),
        ( 2, '05/09/2009', '12:09:09', 500 ),
        ( 4, '05/12/2009', '12:09:09', 400 ),
        ( 7, '05/12/2009', '12:09:09', 500 ),
        ( 8, '05/12/2009', '12:09:09', 200 ),
        ( 9, '09/12/2009', '12:09:09', 500 ),
        ( 2, '01/01/2010', '12:09:09', 5500 )

Following query will show the patient and patientVisits temporary virtual tables.

SELECT  *
FROM    @PATIENT
SELECT  *
FROM    @PATIENTVISITS

Note: This should be done in one execution because above tables are virtual tables.

Following  query displays the Patient ID, patient Name, total sum of each patient of all visits.

SELECT  ID,fullname,SUM(DOCTOR_FEE)
FROM    @PATIENT P ,
       @PATIENTVISITS PV
WHERE   P.ID = PV.PATIENT_ID
GROUP BY GROUPING SETS((ID,fullname))

Output

Following query will returns All Patient ID, Patient Name, Doctor fee and grand total of All patients.

SELECT  ID,fullname,SUM(DOCTOR_FEE)
FROM    @PATIENT P ,
       @PATIENTVISITS PV
WHERE   P.ID = PV.PATIENT_ID
GROUP BY GROUPING SETS((ID,fullname),VISIT_DATE )

Output

Following query will returns year wise doctor fee.

SELECT  VISIT_DATE ,
       SUM(DOCTOR_FEE)
FROM    @PATIENT P ,
       @PATIENTVISITS PV
WHERE   P.ID = PV.PATIENT_ID
GROUP BY GROUPING SETS(( VISIT_DATE ))

Output

Following query shows the year wise patient id,Patient name, year and total of doctor fee of all patients at the end of each year and grand total at the end of row.

SELECT  id ,
       fullname ,
       YEAR(visit_date) ,
       SUM(DOCTOR_FEE)
FROM    @PATIENT P ,
       @PATIENTVISITS PV
WHERE   P.ID = PV.PATIENT_ID
GROUP BY GROUPING SETS(( id ,
                        fullname ,
                        YEAR(visit_date)
                      ), id, ( ))

Output

Following query will returns each patient records yearly group wise and total of doctor fee of each patient at the end of year.

SELECT  id ,
       fullname ,
       YEAR(visit_date) AS [visit date] ,
       SUM(DOCTOR_FEE) AS [Fee]
FROM    @PATIENT P ,
       @PATIENTVISITS PV
WHERE   P.ID = PV.PATIENT_ID
GROUP BY GROUPING SETS(( id ,
                        fullname ,
                        YEAR(visit_date)
                      ), ( id ), ( YEAR(visit_date) ), ( ))

Output

Following query will return each month of year group of each patient records and total fee of doctor at each month and grand total of doctor fee at the end of row.

SELECT  id ,
       fullname ,
       MONTH(visit_date) AS [MONTH] ,
       YEAR(visit_date) AS [Year] ,
       SUM(DOCTOR_FEE) AS [Fee]
FROM    @PATIENT P ,
       @PATIENTVISITS PV
WHERE   P.ID = PV.PATIENT_ID
GROUP BY GROUPING SETS(( id ,
                        fullname ,
                        ROLLUP(YEAR(visit_date), MONTH(visit_date))
                      ))

Output

Conclusion

Grouing sets is avaible in ms sql 2008, before going to start grouping set, you must have knowledge of grouping sets.

Download

Case 1 (1.92 kb)

Case_2.sql (2.04 kb)

Case_3.sql (2.07 kb)

Case_4.sql (2.05 kb)

Case_5.sql (2.19 kb)

Case_6.sql (2.26 kb)

Case_7.sql (2.29 kb)