An aggregate function performs a calculation on a set of values and returns a single value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause divides the rows into groups, and the aggregate function is applied to each group.
SQL provides several aggregate functions that you can use to perform calculations on your data.
| Function | Description |
|---|---|
COUNT() |
Returns the number of rows that matches a specified criterion. |
SUM() |
Returns the total sum of a numeric column. |
AVG() |
Returns the average value of a numeric column. |
MIN() |
Returns the smallest value of the selected column. |
MAX() |
Returns the largest value of the selected column. |
We will use the Products table for the examples in the next few chapters.
Products Table:
| ProductID | ProductName | CategoryID | Price |
|---|---|---|---|
| 1 | Chai | 1 | 18.00 |
| 2 | Chang | 1 | 19.00 |
| 3 | Aniseed Syrup | 2 | 10.00 |
| 4 | Chef Anton's Cajun Seasoning | 2 | 22.00 |
| 5 | Grandma's Boysenberry Spread | 2 | 25.00 |
Imagine you want to find the average price of all products. Instead of fetching all the prices and calculating the average in your application code, you can ask the database to do it for you.
SELECT AVG(Price) FROM Products;
This is much more efficient because the database is highly optimized for these kinds of calculations, and it reduces the amount of data that needs to be sent over the network from the database to your application.
In the following chapters, we will look at each of these aggregate functions in detail with more examples.
Important: Aggregate functions ignore `NULL` values in their calculations (except for `COUNT(*)`, which counts all rows).