# Zero/NULL Case Trick

In the book Getting Started with SQL, Thomas Nield talks about a technique he calls the zero/null case trick:

There is a simple but powerful tool that can apply different filtering conditions to different aggregations. We can create separate total counts when a tornado was present versus not present in two separate columns:

``````SELECT year, month,
SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation,
SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitation
FROM station_data
WHERE year >= 1990
GROUP BY year, month
``````

What we have effectively done is get rid of the WHERE conditions when tornado = 1 or tornado = 0, and then move those conditions to CASE expressions inside the SUM() functions. If the condition is met, the precipitation value is added to the sum. If not, a 0 is added, having no effect. We do these for both columns, on for when a tornado was present and one for when a tornado was not present respectively.

You can make the CASE expression have as many condition/value pairs as you need, giving you the ability to do highly particular interceptions of values with your aggregations. You can also use this trick to emulate crosstabs and pivot tables, expressing aggregations into separate columns rather than in rows. A common example of this is doing current year/previous year analysis, as you can express separate years with different columns.

As a novice, that technique seems like it could come in really handy for summarizing data. I want to look up that technique online to get more information.

The author of that book calls the technique the "zero/null case trick". But when I google that term, I don't get many results.

Question:

Is there a generally accepted name for that technique? (that would yield more results when searching online)

• Other handy functions you may want to investigate are NULLIF and COALESCE Sep 6, 2021 at 16:44
• Sep 6, 2021 at 16:44

It may be either written explicitly using `CASE` or equivalently using `FILTER` syntax introduced with SQL:2003 e.g. `COUNT(*) FILTER (WHERE tornado = 1)`. Currently Postgres, HSQLDB and SQLite support the new syntax.
Note that it is not necessarily the best way to do it, there might be other techniques like conditional aggregation (`FILTER`) and window functions available. (But I don't know anything about Oracle.)