Databases and SQL
- 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
SELECT dated FROM Visited;
but to combine them, we must use an aggregation function such as
max. 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;
max are just two of the aggregation functions built into SQL. Three others are
SELECT avg(reading) FROM Survey WHERE quant='sal';
SELECT count(reading) FROM Survey WHERE quant='sal';
SELECT sum(reading) FROM Survey WHERE quant='sal';
count(reading) here, but we could just as easily have counted
quant 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(reading), max(reading) FROM Survey WHERE quant='sal' AND reading<=1.0;
We can also combine aggregated results with raw results, although the output might surprise you:
SELECT person, count(*) FROM Survey WHERE quant='sal' AND reading<=1.0;
Why does Lake’s name appear rather than Roerich’s or Dyer’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 here where the 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. 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(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. We know that this doesn’t work:
SELECT person, count(reading), round(avg(reading), 2) FROM Survey WHERE quant='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, she 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 if she ever had a data set with fifty or five hundred scientists, the chances of her 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, 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 one to display alongside the aggregated
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, 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:
selected records from the
Surveytable where the
personfield was not null;
grouped those records into subsets so that the
quantvalues in each subset were the same;
ordered those subsets first by
person, and then within each sub-group by
counted the number of records in each subset, calculated the average
readingin each, and chose a
quantvalue 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?
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?
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 reading - avg(reading) FROM Survey WHERE quant='rad';
What does this actually produce, and why?
Ordering When Concatenating
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 order the list by surname?