Aggregate functions turn many rows into a useful summary. They answer practical questions such as how many employees are recorded, how much revenue was collected, and what the average order value is. This lesson explains what each common aggregate calculates, how NULL affects the result, and when DISTINCT changes the meaning.
Key Takeaways at a Glance:
- •Aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarize multiple input rows into a single scalar value per group.
- •COUNT(*) counts every physical row; COUNT(column) counts only rows where the specified column is NOT NULL.
- •SUM and AVG ignore NULL values entirely; if all rows in a group are NULL, SUM/AVG return NULL.
- •COUNT(DISTINCT column) computes the cardinality of unique non-NULL values.
In Simple Words (The Mental Model)
Think of aggregate functions as a blender. You drop 100 fruit slices (rows) into the blender, press blend, and get one single cup of juice (summary number). SUM gives the total weight, AVG gives average sweetness, and COUNT tells you how many slices went in.
Real-World Analogy
Think of a class test score sheet. COUNT(*) counts how many students are enrolled. COUNT(score) counts how many actually showed up to take the exam. AVG(score) computes the class average among test-takers only (ignoring absent students rather than giving them a 0 unless you use COALESCE).