Getting Started with SQL

Chapter 6. GROUP BY and ORDER BY

Aggregating data (also referred to as rolling up, summarizing, or grouping data) is creating some sort of total from a number of records. Sum, min, max, count, and average are common aggregate operations. In SQL you can group these totals on any specified columns, allowing you to control the scope of these aggregations easily.

Grouping Records

First, perform the simplest aggregation: count the number of records in a table. Open the SQL editor and get a count of records for station data:

SELECT COUNT(*) AS record_count FROM station_data;

The COUNT(*) means to count the records. We can also use this in combination with other SQL operations, like WHERE. To count the number of records where a tornado was present, input the following:

SELECT COUNT(*) AS record_count FROM station_data
WHERE tornado = 1;

We identified 3,000 records with tornadoes present. But what if we wanted to separate the count by year (Figure 6-1)? We can do that too with this query:

SELECT year, COUNT(*) AS record_count FROM station_data
WHERE tornado = 1
GROUP BY year;

Figure 6-1. Getting a tornado count by year

This data suddenly becomes more meaningful. We now see the tornado sighting count by year. Let’s break down this query to see how this happened.

First, we select the year, then we select the COUNT(*) from the records, and we filter only for records where tornado is ...