Aggregate functions
The aggregate functions — COUNT
, SUM
, AVG
, MAX
, MIN
and LIST
— don’t handle NULL
in the same way as ordinary functions and operators.Instead of returning NULL
as soon as a NULL
operand is encountered, they only take nonNULL
fields into consideration while computing the outcome.That is, if you have this table:
MyTable  

ID 
Name 
Amount 
1 
John 
37 
2 
Jack 

3 
Jim 
5 
4 
Joe 
12 
5 
Josh 

...the statement select sum(Amount) from MyTable
returns 54, which is 37 + 5 + 12.Had all five fields been summed, the result would have been NULL
.For AVG
, the nonNULL
fields are summed and the sum divided by the number of nonNULL
fields.
There is one exception to this rule: COUNT(*)
returns the count of all rows, even rows whose fields are all NULL
.But COUNT(FieldName)
behaves like the other aggregate functions in that it only counts rows where the specified field is not NULL
.
Another thing worth knowing is that COUNT(*)
and COUNT(FieldName)
never return NULL
: if there are no rows in the set, both functions return 0.COUNT(FieldName)
also returns 0 if all FieldName
fields in the set are NULL
.The other aggregate functions return NULL
in such cases.Be warned that SUM
even returns NULL
if used on an empty set, which is contrary to common logic (if there are no rows, the average, maximum and minimum are undefined, but the sum is known to be zero).
Now let’s put all that knowledge in a table for your easy reference:
Function 
Results 


Empty set 
All 
Other sets or columns 


0 
Total number of rows 
Total number of rows 

0 
0 
Number of rows where 



Max or min value found in the column 



Sum of non 



Average of non 



Commaseparated string concatenation of non 