# 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

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.

 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.