Using SQL's CASE to extract data useful for statistical analysis from a database

This short article describes the use of the SQL CASE statement, presenting an archaeological case study.

CASE is a very powerful and often overlooked construct of the SQL language, which allows a series of given conditions to be evaluated and returns a value as soon as the first condition is satisfied, exactly like if-then-else statements in many other programming languages.

When a condition is met, the process stops and returns the corresponding given result. If no condition is met, the value contained in the ELSE clause is returned.

If, finally, the ELSE part is not provided, the value NULL is returned (which is different from the string 'NULL').

The syntax of CASE is as follows:

1
CASE
2
   WHEN condition_1 THEN "something"
3
   WHEN condition_2 THEN "something else"
4
   ...
5
   ELSE "default value"
6
END

An example is worth a thousand explanations, so in the following paragraphs I introduce a real case study, the very one that led me to discover this function.

Specifically, I was working on a database relating to the Roman necropolis of the Roman city of Suasa and needed to extract some data to create fairly simple charts. In particular, I needed to quickly extract the total number of tombs broken down by phase, and within each phase the total number of inhumation burials and cremation burials. The end goal was to obtain a bar chart showing, for each phase, the total number of tombs for each of the two burial rites.

Below is an excerpt of the structure of the reference table from which to extract this information. The table is more complex, but here I only extract the columns that interest us.

Query:

1
SELECT id,
2
       nome,
3
       rito,
4
       fase
5
  FROM suasa__tombe;

Result:

Here, then, is the query to extract the data we need:

1
SELECT SUM(CASE WHEN rito = 'inumazione' THEN 1 ELSE 0 END) AS inumazioni,
2
       SUM(CASE WHEN rito = 'cremazione' THEN 1 ELSE 0 END) AS cremazioni,
3
       fase
4
  FROM suasa__tombe
5
 GROUP BY fase;

And finally, here are the results of the query, ready to be turned into a chart:

In this case we are using the CASE statement in the column definition, together with the SUM function, which calculates totals.

In detail, SUM(CASE WHEN rito = 'inumazione' THEN 1 ELSE 0 END) AS inumazioni defines a column to which the label or alias inumazioni is assigned, via the AS statement (follow this link for more information on AS), purely to make the output data easier to read.

The column contains a sum, whose elements are defined by CASE. For each row of the database, it is evaluated whether the boolean expression rito = 'inumazione' is true or false — in other words, whether the rito field contains exactly the value inumazione. If the answer is positive (boolean value TRUE), then (THEN) the number 1 is returned to the sum function (one unit is added to the inhumation count); otherwise (ELSE) 0 is returned (nothing is effectively added to the inhumation count).

The same reasoning applies to the second column, defined as: SUM(CASE WHEN rito = 'cremazione' THEN 1 ELSE 0 END) AS cremazioni, where only the label changes, and of course the reference value of the rito field.

We spoke above of counting values for each row, because in this query we are grouping values, specifically grouping them by the fase field, as is clear from the last part of the main query, GROUP BY fase, which adds a grouping principle (follow this link for more information on GROUP BY).

With CASE you can use all SQL operators (e.g. =, <, >, <=, >=, LIKE, etc.) and it is possible to chain multiple conditions using the usual AND or OR. What matters is that the expression between WHEN and THEN always returns a boolean value of TRUE or FALSE. The case shown above was fairly simple, but it is also possible to have several WHEN...THEN statements.

References

• SQL Tutorial: https://www.sqltutorial.org/sql-case/
• Free Code Camp: https://www.freecodecamp.org/news/case-statement-in-sql-example-query/
• W3 Schools: https://www.w3schools.com/sql/sql_case.asp