Aggregate functions in SQL

In the previous tutorial, we learned about GROUP BY, a clause that seems terribly benign until you use the aggregate functions we'll learn about in this tutorial.

With COUNT, SUM, AVERAGE, and other functions, SQL will be able to provide most of the analytical functionality we have with Pivot Tables, with considerably more speed and flexibility.

Note: For this SQL lesson, I will be using the Sequel Pro GUI for the MySQL database engine and will be querying the SFPD incident reports categorized as ASSAULT

If you want to see exactly the same results I do, you'll want to download and import the__ MySQL database of 2003 to 2013 assault reports from the SFPD__.

I've also created the SQLite version of that database, which should functionally be the same as the MySQL version.

For both MySQL and SQLite, I've also created a database of all the SFPD reports from 2003 to 2013. All the queries should work the same, except you can explore all the different categories of crime. The tradeoff is that the database is much bigger, and so will be slower to download, import, and query. If you're completely new to all this, I would just go with the assaults database, just so any errors you make don't take even longer to figure out.

• MySQL assault reports: 130,097 rows
• MySQL all incident reports: 1,491,764 rows
• SQLite assault reports: 130,097 rows
• SQLite all incident reports: 1,491,764 rows

The COUNT

Let's review a basic use of GROUP BY; the following query will list all the police districts that show up in the sfpd_incidents set:

SELECT PdDistrict
  FROM sfpd_incidents
  GROUP BY PdDistrict

Which results in this:

If we want to find the number of incidents per district, then we use the aggregate function COUNT, which we specify in the query as if it were another column to SELECT:

SELECT PdDistrict, COUNT(*)
  FROM sfpd_incidents
  GROUP BY PdDistrict

Which results in:

Note: For many variants of SQL, the syntax COUNT(1) is no different than COUNT(*). I use both interchangeably.

When used with GROUP BY, the COUNT function will provide the number of records that belong to each grouping. Try querying to find the count of incidents per district by subcategory (of assault):

SELECT PdDistrict, Descript, COUNT(1)
  FROM sfpd_incidents
  GROUP BY PdDistrict, Descript

An excerpt of the results:

Counting without grouping

A quick aside: if COUNT is something that can be selected as a result column, what happens if we use it without GROUP BY?

The value returned by COUNT will be the number of result rows. Here is its simplest form:

SELECT COUNT(*)

To find the total number of rows in a given table:

SELECT COUNT(*) FROM sfpd_incidents

And if we filter the table with WHERE, we can expect the count of results to change:

SELECT COUNT(*) FROM sfpd_incidents WHERE PdDistrict='CENTRAL';

SQL vs Pivot Tables

This should look familiar to what you've done with pivot tables: creating rows with a grouping by PdDistrict and Descript, and showing the COUNT values of the grouping.

What SQL does not do very well is create a matrix, e.g. PdDistrict as rows, and Descript as headers. To see something like that, you're better off exporting from SQL and into a spreadsheet.

MAX, MIN, AVG, and SUM

Standard SQL includes functions to find the maximum, minimum, average, and summation of numerical columns. In the sfpd_incidents dataset, we don't have much use for these functions as we don't have many numerical columns to aggregate upon. But they would be handy in a dataset like schools and yearly test scores, for example.

These functions take the same form as COUNT, though you will want to provide the name of a column to find the max/min/average/sum of.

The query below will find the average, max, min of each police district's X and Y coordinates (where neither X nor Y are set to 0) in their respective incident reports:

SELECT PdDistrict, 
    MAX(X), AVG(X), MIN(X), 
    MAX(Y), AVG(Y), MIN(Y)
  FROM sfpd_incidents
  WHERE X != 0 AND Y != 0
  GROUP BY PdDistrict

The result:

Column aliases with AS

You may have noticed that the column headers in a result set reflect what we've supplied to the SELECT statement. For example:

SELECT PdDistrict, COUNT(*)
  FROM sfpd_incidents
  GROUP BY PdDistrict

– produces a results table with these headers:

When exporting SQL results to a different program, it's convenient to format the headers to our liking. Most commonly, this means using more human-readable names, or aliases. In SQL, we do this with the AS keyword after the column/value we wish to alias:

SELECT 
    PdDistrict AS 'Police District', 
    COUNT(*) AS count_of_incidents
  FROM sfpd_incidents
  GROUP BY PdDistrict

The resulting headers:

+—————–+——————–+ | Police District | count_of_incidents | +—————–+——————–+ | | 833 | | BAYVIEW | 16094 | | CENTRAL | 11749 | | INGLESIDE | 14711 | | MISSION | 18650 |

Besides being a nice way to clean up our data results, aliasing will become very important as we deal with joining data from multiple tables, or if joining a table to itself. Aliases become a way to clarify which thing we're referring to in dependent clauses, such as HAVING.

HAVING

We know how to create a query that will return the number of rows as grouped by district and by subcategory of assault:

SELECT PdDistrict, Descript, COUNT(1)
  FROM sfpd_incidents
  GROUP BY PdDistrict, Descript

What if we want to filter the result set so that only the groups with more than 2,000 records are returned? Of course, we could do this by executing the query above, exporting to a spreadsheet, and then sorting and manually deleting rows.

But sometimes it's necessary to do this inside of SQL. For that, we have the HAVING clause, which works similar to the WHERE clause:

SELECT PdDistrict, Descript, COUNT(1) AS count_of_incidents
  FROM sfpd_incidents
  GROUP BY PdDistrict, Descript
  HAVING count_of_incidents > 2000

Note how I aliased the COUNT(1) function as count_of_incidents and used that same alias in the HAVING clause. The result of this query:

Why not WHERE?

So, the first question we might have is: if HAVING works similar to WHERE, then why have HAVING when we could just use WHERE? For example:

SELECT PdDistrict, Descript, COUNT(1) AS count_of_incidents
  FROM sfpd_incidents
  WHERE count_of_incidents > 2000
  GROUP BY PdDistrict, Descript  

In MySQL, you'll receive this error:

ERROR 1054 (42S22): Unknown column 'count_of_incidents' in 'where clause'

Apparently, column aliases aren't yet resolved by the time the WHERE clause does its business, so thus an alias will be "unknown" in the WHERE clause.

OK, no problem. count_of_incidents is merely COUNT(1), so let's not even use the alias:

SELECT PdDistrict, Descript, COUNT(1)
  FROM sfpd_incidents
  WHERE COUNT(1) > 2000
  GROUP BY PdDistrict, Descript  

This will return an even more opaque error message:

ERROR 1111 (HY000): Invalid use of group function

To make sense of this error message, think of where WHERE occurs in the query. It happens before the GROUP BY clause. Without knowing the exact internals of the SQL engine, we might speculate that the COUNT(1) function doesn't get computed until the GROUP BY clause. And so WHERE just can't make sense of the COUNT(1) function.

Also, remember that WHERE is used to filter down a dataset based on columnar values, for example, to retrieve just the records from the CENTRAL and MISSION districts:

SELECT PdDistrict, Descript, COUNT(1) AS count_of_incidents
  FROM sfpd_incidents
  WHERE PdDistrict IN('CENTRAL', 'MISSION')
  GROUP BY PdDistrict, Descript  

Presumably, we want the COUNT aggregate of this filtered set, so it makes sense that we have another way to do a conditional statement – i.e. HAVING – and that it should appear after the GROUP BY:

SELECT PdDistrict, Descript, COUNT(1) AS count_of_incidents
  FROM sfpd_incidents
  WHERE PdDistrict IN('CENTRAL', 'MISSION')
  GROUP BY PdDistrict, Descript
  HAVING count_of_incidents > 1000

Note: We could place the conditional statements in HAVING and not even have the WHERE clause:

SELECT PdDistrict, Descript, COUNT(1) AS count_of_incidents
  FROM sfpd_incidents
  GROUP BY PdDistrict, Descript
  HAVING count_of_incidents > 1000 
    AND PdDistrict IN('CENTRAL', 'MISSION') 

This technically returns the same result set. There are reasons (some of them related to performance) not to do this, though I'm not an expert on how different flavors of SQL react to it. But please, let HAVING have just the aggregate and aliased values, and leave the rest to WHERE.

Conclusion

With aggregate functions, we've reached roughly the same ability to summarize a dataset as we did with spreadsheets and pivot tables. But the syntax we've covered is non-trivial, to say the least. Keep practicing, and mind where you have put your WHERE and HAVING clauses.