top of page

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.


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