Home

Articles
Tutorials

SQLite SQL Tutorials

More SQL

Nikolai Shokhirev

- Basic SQL
- More SQL
- SQL joins
- Triggers

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

 
- Basic SQL
- More SQL
- SQL joins
- Triggers

 

© Nikolai Shokhirev, 2001 - 2024

Count:

 

>