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)