top of page

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


Recent Posts

See All
NoSQL

NoSQL (not only SQL) or non-relational databases are increasingly being used by financial services businesses and businesses in other...

 
 

Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

​

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

​

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page