Grouping in SQL

At this point, we know how create flexible, fine-tuned filters for our database queries using the WHERE clause and LIKE operators (among many other operators). But we still can't do some of the basic functions that even a Pivot Table newbie can do, such as: get a count of incident reports by year, or, listing all the ways a report of THREATS TO SCHOOL TEACHERS is resolved, or, of course, counting all the ways those threats have been resolved, by year, by police district, and so forth.

In this lesson, we learn how to simply group the records. We won't do anything particularly interesting, but as this adds an entirely new clause to our already length SQL queries, it's worth slowing down a bit.

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

Concept of GROUP BY

Before we actually look at the syntax of the GROUP BY clause, it's important to understand the concept: it will, well, group together the records that share the same value in a given column.

Let's take a very simple dataset:

If we were to GROUP BY the Fruit, and simply show the resulting Fruit column, it would look like this, because there are only three values of Fruit:

Similarly, if we were to GROUP BY the Season column, the result would be:

Now, if we were to GROUP BY two columns – Fruit and Is_Profitable – and then show the resulting Fruit and Is_Profitable columns, the results would give us every combination of Fruit and Is_Profitable value:

Because there is not a row in which the Fruit is 'Pineapples' and the Is_Profitable is equal to 'Yes', only 5 results out of a possible 6 combinations are shown.

Syntax of GROUP BY

Among the keywords and clauses we've learned so far, GROUP BY comes at the very end. To group the sfpd_incidents by PdDistrict – without a WHERE clause, we write:

SELECT * FROM sfpd_incidents
GROUP BY PdDistrict

Which results in:

Only 11 rows are returned: one for each of the 10 police districts and 1 row to represent records that have an empty PdDistrict.

The first thing to realize is: none of the other columns in this table are relevant. For example, this result row corresponding to the 'Tenderloin' police district:

– happens to have an IncidntNum of 030204329 and Resolution of 'ARREST, BOOKED' probably because incident number 030204329 is the first incident in our data table that had a PdDistrict of 'TENDERLOIN'.

In other words, you should typically just SELECT the columns you're grouping by (and any aggregate calculations you might do, which we'll get to later):

SELECT PdDistrict FROM sfpd_incidents
GROUP BY PdDistrict

Grouping by multiple columns

To group by multiple columns, supply a comma-separated list of columns. The following query will return a list of every combination of Descript and Resolution:

SELECT Descript, Resolution FROM sfpd_incidents
GROUP BY Descript, Resolution

This will result in 321 result rows. Note that the order of Descript and Resolution in the GROUP BY clause doesn't matter, just as "x times y" is equal to "y times x"

With WHERE

The WHERE clause comes before the GROUP BY clause, which means that only rows that are accepted by the filter will end up in the grouping fun.

For example, we previously retrieved a list of all police districts with this query:

SELECT PdDistrict FROM sfpd_incidents
GROUP BY PdDistrict

Now, to get a list of all the police districts that dealt with at least one reported 'BATTERY' committed against a police officer:

SELECT PdDistrict, Descript  
  FROM sfpd_incidents
  WHERE Descript = 'BATTERY OF A POLICE OFFICER'
  GROUP BY PdDistrict

Note that we only GROUP BY the PdDistrict column because there will only be one Descript value to group against anyway.

The result is 11 rows (so, apparently all of them):

+————+—————————–+ | PdDistrict | Descript | +————+—————————–+ | | BATTERY OF A POLICE OFFICER | | BAYVIEW | BATTERY OF A POLICE OFFICER | | CENTRAL | BATTERY OF A POLICE OFFICER | | INGLESIDE | BATTERY OF A POLICE OFFICER | | MISSION | BATTERY OF A POLICE OFFICER | | NORTHERN | BATTERY OF A POLICE OFFICER | | PARK | BATTERY OF A POLICE OFFICER | | RICHMOND | BATTERY OF A POLICE OFFICER | | SOUTHERN | BATTERY OF A POLICE OFFICER | | TARAVAL | BATTERY OF A POLICE OFFICER | | TENDERLOIN | BATTERY OF A POLICE OFFICER |

'BATTERY OF A POLICE OFFICER' is only one possible police-officer-involved Descript value. Let's use a wildcard to catch all the variations and GROUP BY two fields: PdDistrict and Descript:

SELECT PdDistrict, Descript  
  FROM sfpd_incidents
  WHERE Descript LIKE '%POLICE OFFICER%'
  GROUP BY PdDistrict, Descript

Result:

If we wanted to find the number of police districts that had at least one POLICE OFFICER incident in the year 2006, we just add to the WHERE conditions:

SELECT PdDistrict, Descript  
  FROM sfpd_incidents
  WHERE 
    Descript LIKE '%POLICE OFFICER%'
    AND Date LIKE '2006%'
  GROUP BY PdDistrict, Descript

Note that again, we don't have to group by (or SELECT) the Date field because the WHERE filter should limit all records to those that happened in 2006.

Conclusion

That's it. If you're wondering, Hey, I don't want to know just if a police district had a certain kind of report. I want to know the count of those reports per district – good idea! We'll cover that in the next lesson on aggregate functions.