Aggregate functions
Aggregate functions are functions that can work on rows of a database.They are used in a similar way to functions in spreadsheets (Microsoft Excel).
These functions will return a value when they are run on a row. They will look at the values in a row and then perform the function on the values.
The most common aggregate functions used are listed below:
| Function | Description |
| AVG ( ) | Returns the average value of a numeric column or expression |
| COUNT ( ) | Returns the number of rows that match the criteria in the WHERE clause |
| MAX ( ) | Returns the largest value of the selected column or expression |
| MIN ( ) | Returns the smallest value of the selected column or expression |
| SUM ( ) | Returns the total sum of a numeric column or expression |
| Function | AVG ( ) |
|---|---|
| Description | Returns the average value of a numeric column or expression |
| Function | COUNT ( ) |
|---|---|
| Description | Returns the number of rows that match the criteria in the WHERE clause |
| Function | MAX ( ) |
|---|---|
| Description | Returns the largest value of the selected column or expression |
| Function | MIN ( ) |
|---|---|
| Description | Returns the smallest value of the selected column or expression |
| Function | SUM ( ) |
|---|---|
| Description | Returns the total sum of a numeric column or expression |
SQL aggregate functions require parameter values in the same way that pre-defined programming functions do. Without a parameter, the aggregate function will give an error.
Take notes of the following points:
- COUNT( ) should always return a positive number or zero. This is due to the fact that you cannot have a negative COUNT( )
- You cannot use an aggregate function in a WHERE clause.
- We can use two or more aggregate expressions in a SELECT statement as shown below:
SELECT MIN (meritPoints), MAX (meritPoints)FROM Pupil;- However, we cannot mix an aggregate function and a non-aggregate function in the SELECT statement. This query would throw an error due to this problem. The following query is incorrect
SELECT firstname, MAX (meritPoints)FROM Pupil;Example
This example will return the average merit points for all pupils in the pupil table and round that to two decimal places:
SELECT ROUND (AVG (meritPoints), 2)FROM Pupil