SpatiaLite logo

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:
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:
Previous Slide Table of Contents Next Slide

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

GNU logo 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.