SQL aggregate functions

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

We can use the COUNT command to input in a new field - the 'AS record_count' part - to count the number of records in a table. So this example:

SELECT year, month, COUNT(*) AS record_count FROM station_data

WHERE tornado = 1

GROUP BY year, month

ORDER BY year, month

So here we begin by selecting fields, counting the total number of records in a table, specifying a value in one column, and then end by grouping records and specifying a sort order from them.

Replacing the asterisk in the parentheses after 'COUNT' with a field name lets the record count only count the non-null values in that field.

SQL Aggregate functions include COUNT(); SUM(); AVG(); MAX(); and MIN() . So in this example we're creating a new field 'total_snow' with the sum of the snow_depth values for individual years.

WHERE cannot be used to filter on aggregated fields. You need to use HAVING instead.

Also note that DISTINCT can be used to get a list of unique records in a field. So if in the SQL Editor you input:

SELECT DISTINCT month FROM station_data

ORDER by month

You'll just a get a list of numbers 1 to 12.