Aggregation

Overview

Teaching: 10 min
Exercises: 10 min

Questions

• How can I calculate sums, averages, and other summary values?

Objectives

• Define aggregation and give examples of its use.

• Write queries that compute aggregated values.

• Trace the execution of a query that performs aggregation.

• Explain how missing data is handled during aggregation.

We now want to calculate ranges and averages for our data. We know how to select all of the dates from the Visit table:

SELECT visit_date FROM Visit;

but to combine them, we must use an aggregation function such as min or max. Each of these functions takes a set of records as input, and produces a single record as output:

SELECT min(visit_date) FROM Visit;

SELECT max(visit_date) FROM Visit;

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

SELECT avg(value) FROM Measurement WHERE type = 'sal';

SELECT count(value) FROM Measurement WHERE type = 'sal';

SELECT sum(value) FROM Measurement WHERE type = 'sal';

We used count(value) here, but we could just as easily have counted type or any other field in the table, or even used count(*), since the 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(value), max(value) FROM Measurement WHERE type = 'sal' AND value <= 1.0;

We can also combine aggregated results with raw results, although the output might surprise you:

SELECT person_id, count(*) FROM Measurement WHERE type = 'sal' AND value <= 1.0;

Why does Dyer’s name appear rather than Roerich’s or Lake’s? The answer is that when it has to aggregate a field, but isn’t told how to, the database manager chooses an actual value from the input set. 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_id, max(value), sum(value) FROM Measurement WHERE type = '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. By extension, if we use sum to add all the values in a set, and any of those values are null, the result should also be null. It’s much more useful, though, for aggregation functions to ignore null values and only combine those that are non-null. This behavior lets us write our queries as:

SELECT min(visit_date) FROM Visit;

instead of always having to filter explicitly:

SELECT min(visit_date) FROM Visit WHERE visit_date IS NOT NULL;

Aggregating all records at once doesn’t always make sense. For example, suppose we suspect that there is a systematic bias in our data, and that some scientists’ radiation readings are higher than others. We know that this doesn’t work:

SELECT person_id, count(value), round(avg(value), 2)
FROM  Measurement
WHERE type = 'rad';

because the database manager selects a single arbitrary scientist’s name rather than aggregating separately for each scientist. Since there are only five scientists, we could write five queries of the form:

SELECT person_id, count(value), round(avg(value), 2)
FROM  Measurement
WHERE type = 'rad'
AND   person_id = 'dyer';

but this would be tedious, and if we ever had a data set with fifty or five hundred scientists, the chances of us getting all of those queries right is small.

What we need to do is tell the database manager to aggregate the hours for each scientist separately using a GROUP BY clause:

SELECT   person_id, count(value), round(avg(value), 2)
FROM     Measurement
WHERE    type = 'rad'
GROUP BY person_id;

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_id, it no longer matters that the database manager is picking an arbitrary one to display alongside the aggregated value 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 quantity measured, for example, we just add another field to the GROUP BY clause:

SELECT   person_id, type, count(value), round(avg(value), 2)
FROM     Measurement
GROUP BY person_id, type;

Note that we have added type 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_id, type, count(value), round(avg(value), 2)
FROM     Measurement
WHERE    person_id IS NOT NULL
GROUP BY person_id, type
ORDER BY person_id, type;

Looking more closely, this query:

1. selected records from the Measurement table where the person_id field was not null;

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

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

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

Counting Temperature Readings

How many temperature readings did Frank Pabodie record, and what was their average value?

Solution

SELECT count(value), avg(value) FROM Measurement WHERE type = 'temp' AND person_id = 'pb';

count(value)	avg(value)
2	-20.0

Averaging with NULL

The average of a set of values is the sum of the values divided by the number of values. Does this mean that the avg function returns 2.0 or 3.0 when given the values 1.0, null, and 5.0?

Solution

The answer is 3.0. NULL is not a value; it is the absence of a value. As such it is not included in the calculation.

You can confirm this, by executing this code:

SELECT AVG(a) FROM (
    SELECT 1 AS a
    UNION ALL SELECT NULL
    UNION ALL SELECT 5);

What Does This Query Do?

We want to calculate the difference between each individual radiation reading and the average of all the radiation readings. We write the query:

SELECT value - avg(value) FROM Measurement WHERE type = 'rad';

What does this actually produce, and can you think of why?

Solution

The query produces only one row of results when we what we really want is a result for each of the readings. The avg() function produces only a single value, and because it is run first, the table is reduced to a single row. The value value is simply an arbitrary one.

To achieve what we wanted, we would have to run two queries:

SELECT avg(value) FROM Measurement WHERE type = 'rad';

This produces the average value (6.5625), which we can then insert into a second query:

SELECT value - 6.5625 FROM Measurement WHERE type = 'rad';

This produces what we want, but we can combine this into a single query using subqueries.

SELECT value - (SELECT avg(value) FROM Measurement WHERE type = 'rad') FROM Measurement WHERE type = 'rad';

This way we don’t have execute two queries.

In summary what we have done is to replace avg(value) with (SELECT avg(value) FROM Measurement WHERE type = 'rad') in the original query.

Using the group_concat function

The function group_concat(field, separator) concatenates all the values in a field using the specified separator character (or ‘,’ if the separator isn’t specified). Use this to produce a one-line list of scientists’ names, such as:

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

Can you find a way to list all the scientists family names separated by a comma? Can you find a way to list all the scientists personal and family names separated by a comma?

List all the family names separated by a comma:

SELECT group_concat(family_name, ',') FROM Person;

List all the full names separated by a comma:

SELECT group_concat(personal_name || ' ' || family_name, ',') FROM Person;

Key Points

• Use aggregation functions to combine multiple values.

• Aggregation functions ignore null values.

• Aggregation happens after filtering.

• Use GROUP BY to combine subsets separately.

• If no aggregation function is specified for a field, the query may return an arbitrary value for that field.