How to exclude data in summaries with MSSQL

You often want to exclude some data from summaries, because that data is used instead of having a proper design, such as '999999' meaning that it should excluded, but we still want to keep track of it.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. go
  3. SELECT sales_date,sales_qty,sales_price
  4. FROM sales
  5. WHERE sales_date > '2007-01-01';
  6. go
sales_date              sales_qty                               sales_price
----------------------- --------------------------------------- ---------------------------------------
2007-06-06 17:01:01.000 2.00                                    11.75
2007-04-06 00:00:00.000 0.00                                    11.75
2007-05-01 00:00:00.000 5.00                                    11.75
2007-07-06 00:00:00.000 1.00                                    11.75
2007-05-03 09:00:00.000 3.00                                    11.75
2007-05-05 04:00:00.000 1.00                                    11.75
2007-05-07 11:00:00.000 2.00                                    11.75
2007-05-08 15:00:00.000 1.50                                    11.75
2007-05-07 11:30:00.000 2.00                                    10.50
2007-05-08 13:30:00.000 1.00                                    10.00
2007-05-09 14:00:00.000 1.25                                    10.00
2007-11-06 10:01:01.000 9.00                                    11.75
2007-05-10 14:00:00.000 5.00                                    10.00
2007-05-11 14:15:00.000 0.00                                    10.00
2007-05-12 15:00:00.000 0.00                                    10.00
2007-05-13 16:00:00.000 1.00                                    10.00
2007-05-08 05:00:00.000 1.00                                    10.00
2007-05-07 00:00:00.000 2.00                                    10.00
2007-05-14 11:00:00.000 3.00                                    10.00
2007-05-11 13:00:00.000 3.50                                    10.00
2007-05-08 05:00:00.000 0.00                                    10.00
2007-05-08 07:00:00.000 2.00                                    10.00
2007-05-08 11:00:00.000 3.00                                    10.00

(23 row(s) affected)

Note that on line 2, 14,15 and 21 have a sales quantity of 0.

NullIf

  • Nullif compares 2 values, if they are equal, SQL Server will display a value of null.
  1. SELECT sales_date AS 'Date',
  2.        NULLIF(sales_qty,0) AS 'Qty',
  3.        COALESCE(CAST(NULLIF(sales_qty,0) AS VARCHAR(8)),'N/A') AS 'New qty',
  4.        sales_price AS 'Price'
  5. FROM sales
  6. WHERE sales_date > '2007-01-01';
  7. go
Date                    Qty                                     New qty  Price
----------------------- --------------------------------------- -------- ---------------------------------------
2007-06-06 17:01:01.000 2.00                                    2.00     11.75
2007-04-06 00:00:00.000 NULL                                    N/A      11.75
2007-05-01 00:00:00.000 5.00                                    5.00     11.75
2007-07-06 00:00:00.000 1.00                                    1.00     11.75
2007-05-03 09:00:00.000 3.00                                    3.00     11.75
2007-05-05 04:00:00.000 1.00                                    1.00     11.75
2007-05-07 11:00:00.000 2.00                                    2.00     11.75
2007-05-08 15:00:00.000 1.50                                    1.50     11.75
2007-05-07 11:30:00.000 2.00                                    2.00     10.50
2007-05-08 13:30:00.000 1.00                                    1.00     10.00
2007-05-09 14:00:00.000 1.25                                    1.25     10.00
2007-11-06 10:01:01.000 9.00                                    9.00     11.75
2007-05-10 14:00:00.000 5.00                                    5.00     10.00
2007-05-11 14:15:00.000 NULL                                    N/A      10.00
2007-05-12 15:00:00.000 NULL                                    N/A      10.00
2007-05-13 16:00:00.000 1.00                                    1.00     10.00
2007-05-08 05:00:00.000 1.00                                    1.00     10.00
2007-05-07 00:00:00.000 2.00                                    2.00     10.00
2007-05-14 11:00:00.000 3.00                                    3.00     10.00
2007-05-11 13:00:00.000 3.50                                    3.50     10.00
2007-05-08 05:00:00.000 NULL                                    N/A      10.00
2007-05-08 07:00:00.000 2.00                                    2.00     10.00
2007-05-08 11:00:00.000 3.00                                    3.00     10.00

(23 row(s) affected)
  • Line 2: NULLIF(sales_qty,0) means that if the sales_qty = 0 then assign it a value of NULL else display the value of sales_qty.
  • Line 3: COALESCE(CAST(NULLIF(sales_qty,0) AS VARCHAR(8)),'N/A'). The cast as varchar() is needed, because SQL Server cannot display string and numbers on the same column. The coalesce allows to change the word NULL to N/A.

NullIf and summaries

  1. SELECT COUNT(NULLIF(sales_qty,0)) AS 'w/o 0',
  2.        COUNT(sales_qty) AS 'With 0'
  3. FROM sales
  4. WHERE sales_date > '2007-01-01';
  5. go
w/o 0       With 0
----------- -----------
19          23
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
  • Note the warning: Null value is eliminated by an aggregate or other SET operation. This is defined in the SQL standards. So by using the NULLIF you can control what value you want to eliminate in the summary. This applies COUNT/SUM/AVG...
  • Note the difference between the w/o 0 counted 19 entries and the With 0 counted 23 entries.
  • The nullif can also be applied to any value or any string. It's often used to display 'N/A' .