GROUP BY, ROLLUP, CUBE, GROUPING SETS in SQL Server

GROUP BY: is an ANSI standard SQL clause to group the result set by one or more consecutive columns.

ROLLUP:  It is a GROUP BY operator, performs an aggregate operation for a hierarchy of values in the selected list.

CUBE:  It is also a GROUP BY operator and it performs an aggregate operation for all possible combinations of values in the selected list.

GROUPING SETS: This is equivalent to ROLLUP and CUBE. It was introduced in SQL Server 2008 version.

RULES:

  1. At least one or more aggregate function should be used with or without non aggregated fields in SELECT list.
    1. COUNT(exp) : Number of records per group.
    2. MIN(exp): Minimum value per group.
    3. MAX(exp): Maximum value per group.
    4. AVG(exp): Average value per group.
    5. SUM(exp): Summation of given values per group.
  2. GROUP BY clause must be assembled with only non-aggregated fields in SELECT list and/or with addition to other hidden columns which are not in the SELECT list.
  3. GROUP BY fields should be in the same style or format, in which the fields are in SELECT list.
    1. Eg: ISNULL(col, 0)
  4. WHERE clause can have only non-aggregated filters and should be used before GROUP BY.
  5. To do a filter on aggregated fields; HAVING clause is must.
  6.  ORDER BY must appear at the end of query statement.
  7. In a GROUP BY query, ORDER BY fields must be choose from the SELECT fields only.

LAB SCRIPT:

empname leadname city state Status
Dhaval Parth Seattle WA Active
Khushbu Parth Seattle WA Active
Avani Parth Boston MA Active
Samrat Parth Seattle WA Obsolete
Sahoo Parth Seattle WA Active
Sanjay Samrat Spokane WA Active
Samrat Parth Seattle WA On Leave
Pratik Avani Boston MA Active
Nirav Avani Boston MA Active
Parth Prasad Seattle WA Active
Sorab Samrat Spokane WA Obsolete
Arpit Parth Seattle WA Active

 

state empcount
MA 3
WA 9
state empcount
MA 3
state empcount
WA 9

 

ROLLUP and GROUPING SETS:

ROLLUP:

LeadName Status EmpCount
Avani Active 2
TOTAL : 2
Parth Active 5
Parth Obsolete 1
Parth On Leave 1
TOTAL : 7
Prasad Active 1
TOTAL : 1
Samrat Active 1
Samrat Obsolete 1
TOTAL : 2
GRAND TOTAL : 12

GROUPING SETS:

LeadName Status EmpCount
Avani Active 2
TOTAL : 2
Parth Active 5
Parth Obsolete 1
Parth On Leave 1
TOTAL : 7
Prasad Active 1
TOTAL : 1
Samrat Active 1
Samrat Obsolete 1
TOTAL : 2
GRAND TOTAL : 12

 

CUBE and GROUPING SETS:

CUBE:

LeadName Status EmpCount
Avani Active 2
Parth Active 5
Prasad Active 1
Samrat Active 1
TOTAL Active 9
Parth Obsolete 1
Samrat Obsolete 1
TOTAL Obsolete 2
Parth On Leave 1
TOTAL On Leave 1
GRAND TOTAL NULL 12
Avani Total 2
Parth Total 7
Prasad Total 1
Samrat Total 2

GROUPING SETS:

LeadName Status EmpCount
Avani Active 2
Parth Active 5
Prasad Active 1
Samrat Active 1
TOTAL Active 9
Parth Obsolete 1
Samrat Obsolete 1
TOTAL Obsolete 2
Parth On Leave 1
TOTAL On Leave 1
GRAND TOTAL NULL 12
Avani Total 2
Parth Total 7
Prasad Total 1
Samrat Total 2

Note: If you have any doubt on this topic, feel free to drop me an email or reply on this post..

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

2 thoughts on “GROUP BY, ROLLUP, CUBE, GROUPING SETS in SQL Server

  • June 7, 2016 at 4:24 pm
    Permalink

    Why is that “GRAND TOTAL’ not coming in the results for GROUPING SETS?

    Reply
    • June 7, 2016 at 5:00 pm
      Permalink

      Thanks for noticing it. Updated the scripts for GROUPING SETS to return GRAND TOTAL.

      Reply

Leave a Reply

%d bloggers like this: