7. Aggregate Functions in SQL

Aggregate functions are essential for summarizing data in SQL. They operate on a set of values and return a single value. Here, we cover key aggregate functions:

• MIN: Returns the smallest value in a given column.
• MAX: Returns the largest value in a given column.
• SUM: Returns the sum of the numeric values in a given column.
• AVG: Returns the average value of a given column.
• COUNT: Returns the total number of values in a given column.
• COUNT(*): Returns the number of rows in a table.

Aggregate functions compute results based on a column of numeric data returned by a SELECT statement. They summarize the results of a selected data column. These functions are integral to the GROUP BY clause but can also be used independently.

7.1. Usage Examples

The following examples demonstrate how to use aggregate functions in SQL:

SELECT AVG(salary) FROM employee;

SELECT AVG(salary)
FROM employee
WHERE title = 'Programmer';

SELECT COUNT(*) FROM employees;

7.2. Review Exercises

1. Select the maximum price of any item ordered in the items_ordered table.
2. Select the average price of all items ordered in December.
3. Count the total number of rows in the items_ordered table.
4. Find the lowest price of tents ordered in the items_ordered table.

7.3. Exercise Answers

SELECT MAX(price) FROM items_ordered;

SELECT AVG(price)
FROM items_ordered
WHERE order_date LIKE '%Dec%';

SELECT COUNT(*) FROM items_ordered;

SELECT MIN(price)
FROM items_ordered
WHERE item = 'Tent';