Lesson 24: Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)

Aggregate functions perform a calculation on a set of rows and return a single summary value. These are essential for reporting and analysis.

The Five Main Functions

1. COUNT(): Counts the number of rows in the set.
2. SUM(): Calculates the sum of values in a numeric column.
3. AVG(): Calculates the average (mean) value of a numeric column.
4. MIN(): Finds the smallest value in a column.
5. MAX(): Finds the largest value in a column.

Example 1: Calculating Totals and Counts

Find the total number of products, the highest price, and the total value of stock.

sql SELECT COUNT(*) AS TotalProducts, -- Counts all rows MAX(Price) AS HighestPrice, SUM(Price * StockQuantity) AS TotalStockValue FROM Products;

COUNT(*) vs. COUNT(ColumnName)

• COUNT(*): Counts every row, including those with NULL values in other columns.
• COUNT(ColumnName): Only counts rows where the specified ColumnName is NOT NULL.

Example 2: Counting Distinct Values

If you want to count how many unique departments exist, use DISTINCT.

sql SELECT COUNT(DISTINCT Department) FROM Employees;