SQL Aggregate Functions

There are five aggregate function defined in the ISO standard for SQL.

1. COUNT: Returns the number of values in a specified column.
2. SUM: Returns the sum of values in a specified column.
3. AVG: Returns the average of the values in a specified column.
4. MIN: Returns the smaller value in a specified column.
5. MAX: Returns the largest value in a specified column.

• Each operates on a single column of a table and returns a single value. 
• COUNT, MIN and MAX apply to numeric and non-numeric fields but SUM and AVG may be used on numeric fields only.
• Apart from COUNT(*) each function eliminates nulls first and operates only on remaining non-null values.
• COUNT(*) counts all rows of a table, regardless of whether nulls or duplicate values occur
• Can use DISTINCT before column name to eliminate duplicates.
• DISTINCT has no effect with MIN/MAX but may have with SUM/AVG.

Format
Count how many rows there are in a table
SELECT COUNT(*) AS "total number of rows"  
FROM table;

Count how many distinct values there are in an attribute.
*Note: This does not work in Microsoft Access.
SELECT COUNT(DISTINCT attribute) AS "number of unique values"
FROM table;

Sum all the values in an attribute
SELECT SUM(attribute) AS "sum of attribute"
FROM table;

Find average of all the values in an attribute
SELECT AVG(attribute) AS "average of attribute"  
FROM table;

Find the smallest value in an attribute
SELECT MIN(attribute) AS "smallest of attribute"  
FROM table;

Find the biggest value in an attribute
SELECT MAX(attribute) AS "biggest of attribute"
FROM table;

Using aggregate function while grouping result based on another attribute
SELECT <aggregate function of attribute1>, attribute2  
FROM table  
GROUP BY attribute2; 

Same as previous but restrict the grouping with a condition 
SELECT <aggregate function of attribute1>,attribute2 
FROM table  
GROUP BY attribute2  
HAVING <aggregate function of attribute with condition>;