SQLite SQL Tutorials

More SQL

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 .