Databases and SQL

Aggregation

We know how to select all dates from the Visited table:

SELECT dated FROM Visited;

If we want to make calculations (such as min, max or avg) that take into account all entries, we must use an aggregation function. Each of these functions takes a set of records as input and produces a single record as output:

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

min and max are just two of the aggregation functions built into SQL. Three others are avg, count, and sum:

SELECT avg(reading) FROM Survey WHERE quant='sal';

SELECT sum(reading) FROM Survey WHERE quant='sal';

SELECT count(reading) FROM Survey WHERE quant='sal';

The function count(reading) gives us the number of entries the column reading, but we could just as easily have counted quant or any other field in the table, or even used count(*), since this particular function doesn’t care about the values themselves, just how many values there are.

SQL lets us do several aggregations at once. We can, for example, find the range of sensible salinity measurements:

SELECT min(reading), max(reading) FROM Survey WHERE quant='sal' AND reading<=1.0;

We can also combine aggregated results with raw results but the output is not what you might hope for:

SELECT person, count(*) FROM Survey WHERE quant='sal' AND reading<=1.0;

Why is there only one name in the output? SQL wants to output a table with the data we requested and every column must have the same number of rows. Because the second column is an aggregate field and we didn’t tell it how to aggregate the first column, the database manager chose an actual value from the input set to display. It might use the first one processed, the last one, or something else entirely.

Another important fact is that when there are no values to aggregate — for example, where there are no rows satisfying the WHERE clause — aggregation’s result is “don’t know” rather than zero or some other arbitrary value:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant='missing';

One final important feature of aggregation functions is that they are inconsistent with the rest of SQL in a very useful way. If we add two values, and one of them is null, the result is null. However, if we use sum to add all the values in a set, and any of those values are null, the aggregation function ignores null values and only combine those that are non-null. This behavior lets us write queries as:

SELECT min(dated) FROM Visited;

instead of always having to filter explicitly:

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

Aggregating all records at once doesn’t always make sense. For example, suppose Gina suspects that there is a systematic bias in her data, and that some scientists’ radiation readings are higher than others. If we want to look at the statistics of each scientist’s measurements separately, we could write five queries of the form:

SELECT person, count(reading), round(avg(reading), 2)
FROM  Survey
WHERE quant='rad'
AND   person='dyer';

but this would be tedious and likely to produce errors if the dataset has many scientists to look at individually.

We can tell the database manager to aggregate the hours for each scientist separately using a GROUP BY clause:

SELECT   person, count(reading), round(avg(reading), 2)
FROM     Survey
WHERE    quant='rad'
GROUP BY person;

GROUP BY does exactly what its name implies: groups all the records with the same value for the specified field together so that aggregation can process each batch separately. Since all the records in each batch have the same value for person, it no longer matters that the database manager is picking an arbitrary entry to display alongside the aggregated reading values.

Just as we can sort by multiple criteria at once, we can also group by multiple criteria. To get the average reading by scientist and type of measurement, for example, we just add another field to the GROUP BY clause:

SELECT   person, quant, count(reading), round(avg(reading), 2)
FROM     Survey
GROUP BY person, quant;

Note that we have added quant to the list of fields displayed, since the results wouldn’t make much sense otherwise.

Let’s go one step further and remove all the entries where we don’t know who took the measurement:

SELECT   person, quant, count(reading), round(avg(reading), 2)
FROM     Survey
WHERE    person IS NOT NULL
GROUP BY person, quant
ORDER BY person, quant;

Looking more closely, this query:

1. selected records from the Survey table where the person field was not null;

2. grouped those records into subsets so that the person and quant values in each subset were the same;

3. ordered those subsets first by person, and then within each sub-group by quant; and

4. counted the number of records in each subset, calculated the average reading in each, and chose a person and quant value from each (it doesn’t matter which ones, since they’re all equal).

SELECT reading - avg(reading) FROM Survey WHERE quant='rad';

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth