SQL Case Operator

Here's a continuation of my review of Getting Started with SQL, an O'Reilly guide by Thomas Nield, last blogged about on November 14, 2016.

In Chapter 7 of the guide, we learn about the SQL Case statement. In this example, we see how a script run in SQLiteStudio does the following:

1. Selects five fields [report_code; year; month; data; and wind_speed] from the table named STATION_DATA. The SELECT command at the beginning references these fields, and then the FROM command at the end pulls them from the table.

2. Creates entries named 'HIGH' in a new field called wind_severity when the wind speed is greater than 40. The new field is created following the END command, after conditions are given between WHEN and THEN after the CASE command.

3. Creates entries named 'MODERATE' in the new field called wind_severity when the wind speed is between 30-40.

4. Lists all other wind speeds as 'LOW' in the new field.

When you enter a simpler script like this, we merely create a new field called 'tornado_precipitation' that lists the number of tornadoes for each month in each year.

SELECT year, month, SUM (precipitation) as tornado_precipitation FROM station_data WHERE tornado = 1 GROUP by year, month

A script like this one below takes into account when a specified value is not listed in a field. So on the second line we're getting the average temperature when there are entries in the RAIN or HAIL fields but not counting the entries in these fields that are null, and putting them in the new 'avg_precipitation_temp' field. The WHERE command restricts the results to where year is higher than 2000.

SELECT month,

AVG(CASE WHEN rain OR hail THEN temperature ELSE null END) AS avg_precipitation_temp,

AVG(CASE WHEN NOT (rain OR hail) THEN temperature ELSE null END) AS avg_non_precipitation_temp

FROM station_data WHERE year > 2000 GROUP BY month