Find us on GitHub

Aggregation

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.

To get an estimate of the amount of time it will take to digitize our collection, we now want to know how many books by a given publisher are in our collection, as well as calculate the total number of pages contained in the collection. We know how to select all of the page numbers from the Works table:

%load_ext sqlitemagic
%%sqlite swclib.db
SELECT Pages FROM Works;

	
578
	

	
 
	

	
532
	

	
503
	

	
258
	

	
685
	

	
534
	

	
400
	

	
460
	

	
309
	

	
857
	

	
320
	

	
440
	

	
218
	

	
501
	

	
 
	

	
708
	

	
760
	

	
691
	

	
321

but to combine them, wee 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. For example, the following statements display the smallest and the largest books in our collection:

%%sqlite swclib.db
SELECT Title, min(Pages) FROM Works;

	
Beginning SQL queries
	218
	


%%sqlite swclib.db
SELECT Title, max(Pages) FROM Works;

	
SQL bible
	857

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

%%sqlite swclib.db
SELECT avg(Pages) FROM Works;

	
504.166666666667
	


%%sqlite swclib.db
SELECT count(Title) FROM Works Where Publisher='Wiley';


	
7
	


%%sqlite survey.db
SELECT sum(Pages) FROM Works Where Publisher='Wiley';

	
3666

We used count(Title) here, but we could just as easily have counted ISBN 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 book sizes published by O'Reilly:

%%sqlite swclib.db
SELECT min(Pages), max(Pages) FROM Works Where Publisher='O''Reilly';

	
320
	691

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

%%sqlite swclib.db
SELECT Title, count(*) FROM Works Where Publisher='O''Reilly';

	
MySQL in a nutshell
	6

Why does this title appear rather than any other published by O'Reilly? 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, aggregation's result is "don't know" rather than zero or some other arbitrary value:

%%sqlite swclib.db
SELECT min(Pages), max(Pages) FROM Works Where Publisher='Hachette';

	
 
	 

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:

%%sqlite swclib.db
SELECT Title, min(Pages) FROM Works;

	
Beginning SQL queries
	218

instead of always having to filter explicitly:

%%sqlite swclib.db
SELECT Title, min(Pages) FROM Works WHERE Pages IS NOT NULL;

	
Beginning SQL queries
	218

Aggregating all records at once doesn't always make sense. For example, if we want to get a breakdown of how many books are in the collection for each publisher, we cannot write:

%%sqlite swclib.db
SELECT Publisher, count(Title) FROM Works;


	
O'Reilly
	20

because the database manager selects a single arbitrary publisher name rather than aggregating results separately for each publisher. Since in our small example there are only ten different publishers, we could write ten statements like:SELECT Publisher, count(Title) FROM Works WHERE Publisher='Faber & Faber'; but this would be tedious, and it wouldn't be practical against a real-life database containing many thousands different publishers.

What we need to do is tell the database manager to aggregate the number of titles for each publisher separately using a GROUP BY clause:

%%sqlite swclib.db
SELECT Publisher, count(Title) FROM Works GROUP BY Publisher

	
Apress
	1
	

	
Belknap Press
	1
	

	
Faber & Faber
	1
	

	
McGraw-Hill
	1
	

	
O'Reilly
	6
	

	
Peachpit
	1
	

	
Sams
	1
	

	
South-Western
	1
	

	
Wiley
	7

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 Publisher, it no longer matters that the database manager is picking an arbitrary one to display alongside the count of Title values.

Just as we can sort by multiple criteria at once, we can also group by multiple criteria. To get a yearly breakdown of the number of titles published by each publisher, we just add another field to the GROUP BY clause:

%%sqlite swclib.db
SELECT Publisher, Date, count(Title) FROM Works GROUP BY Publisher, Date;

	
Apress
	2008
	1
	

	
Belknap Press
	2014
	1
	

	
Faber & Faber
	2014
	1
	

	
McGraw-Hill
	2009
	1
	

	
O'Reilly
	2004
	1
	

	
O'Reilly
	2005
	1
	

	
O'Reilly
	2009
	2
	

	
O'Reilly
	2010
	1
	

	
O'Reilly
	2013
	1
	

	
Peachpit
	2005
	1
	

	
Sams
	2013
	1
	

	
South-Western
	2008
	1
	

	
Wiley
	2005
	1
	

	
Wiley
	2008
	1
	

	
Wiley
	2010
	1
	

	
Wiley
	2011
	2
	

	
Wiley
	2013
	2

Note that we have added Date to the list of fields displayed, since the results wouldn't make much sense otherwise. Let's wrap up our collection analysis query:

%%sqlite swclib.db
SELECT	 Publisher, Date, count(Title), sum(Pages) FROM Works 
WHERE 	 Pages IS NOT NULL 
GROUP BY Publisher, Date 
ORDER BY Publisher ASC, Date DESC;

	
Apress
	2008
	1
	218
	

	
Belknap Press
	2014
	1
	685
	

	
Faber & Faber
	2014
	1
	258
	

	
McGraw-Hill
	2009
	1
	534
	

	
O'Reilly
	2013
	1
	532
	

	
O'Reilly
	2010
	1
	503
	

	
O'Reilly
	2009
	2
	898
	

	
O'Reilly
	2005
	1
	321
	

	
O'Reilly
	2004
	1
	691
	

	
Peachpit
	2005
	1
	460
	

	
South-Western
	2008
	1
	309
	

	
Wiley
	2013
	1
	760
	

	
Wiley
	2011
	2
	1108
	

	
Wiley
	2010
	1
	440
	

	
Wiley
	2008
	1
	857
	

	
Wiley
	2005
	1
	501

Looking more closely, this query:

  1. selected records from the Works table where the Pages field was not null;

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

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

  4. counted the number of records in each subset, calculated the sum of Pages in each, and chose a Publisher and Date value from each to display (it doesn't matter which ones, since they're all equal in each subset).

Challenges

  1. How many books were published in 2011, and what was their average length?

  2. 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?

  3. 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 publishers' names, such as:

    O'Reilly, Wiley, Faber & Faber, Belknap Press, ...

    Can you find a way to order the list alphabetically?

Key Points

  • An aggregation function combines many values to produce a single new value.
  • Aggregation functions ignore null values.
  • Aggregation happens after filtering.
License Blog GitHub Twitter Issues