In SQL, aggregate functions are functions that perform a calculation on a set of values and return a single value. They’re commonly used with the GROUP BY clause to summarize data.
Common Aggregate Functions
- COUNT() – Counts the number of rows
- SUM() – Returns the total sum of a numeric column
- AVG() – Returns the average value of a numeric column
- MIN() – Returns the smallest value
- MAX() – Returns the largest value
Example Table: Sales
| SaleID | Customer | Amount | SaleDate |
|---|---|---|---|
| 1 | John | 100 | 2023-01-10 |
| 2 | Alice | 200 | 2023-01-11 |
| 3 | John | 150 | 2023-01-12 |
| 4 | Alice | 300 | 2023-01-13 |
| 5 | Mark | 250 | 2023-01-14 |
Example Queries with Aggregate Functions
- COUNT() – Count total sales:
SELECT COUNT(*) AS TotalSales
FROM Sales;
➡️ Result: 5
- SUM() – Total revenue:
SELECT SUM(Amount) AS TotalRevenue
FROM Sales;
➡️ Result: 1000
- AVG() – Average sale amount:
SELECT AVG(Amount) AS AverageSale
FROM Sales;
➡️ Result: 200
- MIN() and MAX() – Find smallest and largest sale:
SELECT MIN(Amount) AS MinSale, MAX(Amount) AS MaxSale
FROM Sales;
➡️ Result: MinSale = 100, MaxSale = 300
- GROUP BY with Aggregate Functions – Sales per customer:
SELECT Customer, SUM(Amount) AS TotalSpent, COUNT(*) AS NumberOfPurchases
FROM Sales
GROUP BY Customer;
➡️ Result:
| Customer | TotalSpent | NumberOfPurchases |
|---|---|---|
| John | 250 | 2 |
| Alice | 500 | 2 |
| Mark | 250 | 1 |