SQLite / SQL Tutorials

More SQL

by Nikolai Shokhirev

(work in progress, check for updates)

Up ABC tutorials

Prev: SQL  |  More SQL  |  Next: Joins

Aggregate functions

Aggregate functions perform a calculation on a set of values and return a single value. The most common functions are

COUNT,  MAX,  SUM,  MIN,  STDEV (standard deviation),  AVG (average).

Examples

The select SQL statement gives

sqlite> SELECT ArtistID from CDs;
1
1
2
2
1
3
4   

Total number of records

sqlite> select COUNT(*) from CDs;
7     

The number of not NULL records in a specific column

sqlite> select COUNT(ArtistID) from CDs;
7     

SQLite does not support 

select count(DISTINCT ArtistID) from CDs;    

In order to get  the number of artists we execute the following statement

sqlite> SELECT count(ArtistID) FROM (SELECT DISTINCT ArtistID FROM CDs);
4     

The GROUP BY clause allows getting more detailed information

sqlite> select count(*), ArtistID from CDs GROUP BY ArtistID;
3|1
2|2
1|3
1|4 

Instead of the keyword WHERE, the HAVING clause is used in combination with the GROUP BY clause:

sqlite> select count(*), ArtistID from cds GROUP BY ArtistID HAVING count(*) > 1;
3|1
2|2   

The other aggregate functions can be used in a similar way.

 

More examples

Do exercises from http://www.linuxdevcenter.com/pub/ct/19

 

References

 
Up ABC tutorials

Prev: SQL  |  More SQL  |  Next: Joins

Home | Resumé |  Shokhirev.com |  Computing |  Links |  Publications

©Nikolai Shokhirev, 2004-2009

>