SQL aggregation functions

SQL supports certain aggregation functions. For example, the following query counts the total number of students:

select count(*) from Student;

Using COUNT(*) counts the total number of rows in the table. If we specify a column name, then only non-NULL values in that column will be counted:

select count(grade) from Student;

Other aggregation functions

• max
• min
• avg
• sum

Aggregation and null

Most SQL aggregate functions ignore nulls: sum, avg, min, max, and count(col) all skip rows where the argument is null. The exception is count(*), which takes no column argument and simply counts rows (even when the row is all null).

Warning

One need to be careful when combine a count(*) with other aggregate function which ignore NULLs

-- good
select avg(age) from Student;
 
-- Oops. Will not give the correct result if there is null
select sum(age) / count(*) as avg from Student;
 
-- correct
select sum(age) / count(age) as avg from Student;

Reference

• PostgreSQL: Documentation: 18: 9.21. Aggregate Functions