Zero/NULL Case Trick

Question

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)

Answer 1

It is variously known as:

• conditional sum
• filtered aggregation
• conditional aggregation
• selective aggregation

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.

Answer 2

Excel (and possibly other spreadsheet software) calls this a "pivot table" because you take the row-based groups and essentially pivot them by 90 degrees when you put them into columns.

Since people are familiar with the term it carried over into the SQL world. Example

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.)