Zero/NULL Case Trick

caseoracleoracle-19csumterminology

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)

Best Answer

It is variously known as:

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.

Related Question