How to create empty group with MSSQL

  • I have had many occasions where I only wanted some data, but needed to show into the context of the rest.
  • The standard way has been to use create temporary tables with often some cursor operation to segregate between the data selected and the rest.
  • We have always had the group by for summarizing data, we can even select on the grouping with the having. All of this is how to restrict and shrink the data selected, what about expanding it.
  • Microsoft has provided us with the construct: GROUP BY ALL which allows you to separate your data from the whole data [the context].
  • But what about the context? How big or how small of a group is this? That's where the group by all comes.
  • With the group by all you can create empty groups that will not be part of any summary calculations.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • Microsoft has announced, that starting with SQL Server 2008, the group by all is deprecated, meaning that that group by all is still supported by SQL Server 2008, but will not be supported by the next version of SQL Server 20xx.

Data used

  1. USE sql911;
  2. go
  3. SELECT emp_short, trans_date, debit, credit
  4. FROM trans02;
  5. go
emp_short trans_date              debit                 credit
--------- ----------------------- --------------------- ---------------------
000190    2007-06-01 00:00:00.000 NULL                  125.00
000110    2007-06-01 00:00:00.000 NULL                  100.00
000190    2007-06-02 00:00:00.000 25.00                 NULL
000030    2007-07-15 00:00:00.000 357.00                NULL
000030    2007-07-16 00:00:00.000 NULL                  22.00
000030    2007-07-18 00:00:00.000 NULL                  41.00
000030    2007-07-20 00:00:00.000 NULL                  29.50
000110    2007-07-20 00:00:00.000 42.00                 NULL
000110    2007-07-22 00:00:00.000 66.00                 NULL
000110    2007-07-23 00:00:00.000 77.00                 NULL
000010    2007-07-23 00:00:00.000 11.00                 NULL
000010    2007-07-24 00:00:00.000 22.56                 NULL
000010    2007-07-25 00:00:00.000 33.69                 NULL
000070    2007-09-25 00:00:00.000 NULL                  11.11
000070    2007-09-26 00:00:00.000 NULL                  11.22
000070    2007-09-27 00:00:00.000 55.00                 NULL
000070    2007-10-01 00:00:00.000 52.50                 NULL
000160    2007-10-20 00:00:00.000 9.00                  NULL
000160    2007-10-20 01:00:00.000 11.50                 NULL
000160    2007-10-21 00:00:00.000 NULL                  45.00

(20 row(s) affected)

The standard group by

  1. SELECT emp_short, MAX(trans_date) AS 'Date',
  2.        SUM(debit) AS 'Dr',
  3.        SUM(credit) AS 'Cr'
  4. FROM trans02
  5. WHERE emp_short <= '000100'
  6. GROUP BY emp_short;
  7. go
emp_short Date                    Dr                    Cr
--------- ----------------------- --------------------- ---------------------
000010    2007-07-25 00:00:00.000 67.25                 NULL
000030    2007-07-20 00:00:00.000 357.00                92.50
000070    2007-10-01 00:00:00.000 107.50                22.33
Warning: Null value is eliminated by an aggregate or other SET operation.

(3 row(s) affected)
  • The where and the group by did display the requested data, a summary of the transactions for all the employees whose nummber is less or equal to 100. There are only 3.

Empty groups: group by all

  1. SELECT emp_short, MAX(trans_date) AS 'Date',
  2.        SUM(debit) AS 'Dr',
  3.        SUM(credit) AS 'Cr'
  4. FROM trans02
  5. WHERE emp_short <= '000100'
  6. GROUP BY all emp_short;
  7. go
emp_short Date                    Dr                    Cr
--------- ----------------------- --------------------- ---------------------
000010    2007-07-25 00:00:00.000 67.25                 NULL
000030    2007-07-20 00:00:00.000 357.00                92.50
000070    2007-10-01 00:00:00.000 107.50                22.33
000110    NULL                    NULL                  NULL
000160    NULL                    NULL                  NULL
000190    NULL                    NULL                  NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

(6 row(s) affected)

Note that:

  1. group by all returned 6 rows while the group by returned 3 rows.
  2. Line 5: where emp_short <= '000100' only have data calculated, all the others get a null.
  3. Line 6: group by all emp_short will create the empty groups of employees.
  • Microsoft has announced, that starting with SQL Server 2008, the group by all is deprecated, meaning that that group by all is still supported by SQL Server 2008, but will not be supported by the next version of SQL Server 20xx.