Chapter 3: Aggregate Functions and Window Functions
Think about a school report card. Every student has individual marks in every subject. But the report card also shows the class average, the highest mark, the lowest mark, and the total students who passed. Nobody sat there adding marks one by one. The teacher ran a formula on the whole column and got one number. That is exactly what aggregate functions do in SQL.
Aggregate functions take multiple rows and collapse them into a single value. You have 10,000 orders? COUNT tells you "10,000." SUM tells you the total revenue. AVG tells you the average order value. One function, one answer. The individual rows disappear — you get the summary.
You are testing a dashboard that shows "Total Revenue: 45,00,000." How do you verify that number? You cannot scroll through 10,000 orders and add them manually. You run SELECT SUM(total_amount) FROM orders. Done. One query, one answer, complete verification.
| Function | What It Does | Real Example |
|---|---|---|
| COUNT(*) | Counts all rows | Total orders placed today |
| COUNT(column) | Counts non-NULL values in a column | How many orders have a coupon code |
| SUM(column) | Adds up all values | Total revenue this month |
| AVG(column) | Calculates the mean | Average order value |
| MIN(column) | Finds the smallest value | Cheapest order placed |
| MAX(column) | Finds the largest value | Most expensive order placed |
COUNT(*) counts all rows including NULLs. COUNT(column_name) skips NULLs. This difference matters. If you have 100 orders but only 80 have a coupon_code, COUNT(*) returns 100 while COUNT(coupon_code) returns 80. Use the right one.
Key Point: Aggregate functions collapse many rows into one summary value. They are the backbone of data validation — every time you verify a total, a count, or an average on the UI, you are mentally comparing it to what an aggregate function would return.
Key Point: Aggregate functions collapse many rows into one value. COUNT, SUM, AVG, MIN, MAX are the five you will use daily.