Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	Understanding aggregate functions
2011 January 28

Previous Slide

Table of Contents

Next Slide

We've seen since now how SQL allows to retrieve single values on a row-per-row basis.
A different approach is supported as well, one allowing to compute total values for the whole table, or for group(s) of selected rows.
This implies using some special functions, known as aggregate functions.

SELECT Min(POP2001), Max(POP2001),
Avg(POP2001), Sum(POP2001), Count(*)
FROM com2001_s;

This query will return a single row, representing something like a summary for the whole table:

the Min() function will return the minimum value found into the given column
the Max() function will return the maximum value found into the given column
the Avg() function will return the average value for the given column
the Sum() function will return the total corresponding to the given column
the Count() function will return the number of rows (entities) found

SELECT COD_PRO, Min(POP2001), Max(POP2001),
Avg(POP2001), Sum(POP2001), Count(*)
FROM com2001_s
GROUP BY COD_PRO;

You can use the GROUP BY clause in order to establish a more finely grained aggregation by sub-groups.
This query will return distinct totals for each County.

SELECT COD_REG, Min(POP2001), Max(POP2001),
Avg(POP2001), Sum(POP2001), Count(*)
FROM com2001_s
GROUP BY COD_REG;

And you can obviously get totals for each Region simply changing the GROUP BY criterion.

SELECT DISTINCT COD_REG, COD_PRO
FROM com2001_s
ORDER BY COD_REG, COD_PRO;

There is another different way to aggregate rows: i.e. using the DISTINCT clause.
Please note well: this isn't absolutely the same as using a GROUP BY clause:

DISTINCT simply suppresses any duplicate row, but has nothing to do with aggregate functions.
GROUP BY is absolutely required each time you have to properly control aggregation.

Previous Slide

Table of Contents

Next Slide

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.