SQL Aggregate Functions — Study Notes
SQL Aggregate Functions
SUM · MIN · AVG · MAX · COUNT
1. What Are Aggregate Functions?
Aggregate functions perform a calculation on a set of values and return a single value. They are
commonly used with the GROUP BY clause to group rows sharing a property.
Quick Reference
Function Returns Ignores NULLs? Works On
SUM() Total of all values Yes Numeric only
MIN() Smallest value Yes Numeric, text, date
AVG() Mean of all values Yes Numeric only
MAX() Largest value Yes Numeric, text, date
COUNT(*) Number of rows No (counts all) Any
COUNT(col) Non-NULL row count Yes Any
2. SUM( )
Returns the total sum of a numeric column. NULL values are ignored automatically.
Syntax
SELECT SUM(column_name) FROM table_name;
Examples
-- Total revenue from all orders
SELECT SUM(amount) AS total_revenue
FROM orders;
-- Revenue per customer
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
-- Revenue for completed orders only
SELECT SUM(amount) AS completed_revenue
Page 1 of 5
SQL Aggregate Functions — Study Notes
FROM orders
WHERE status = 'completed';
Key Notes
• Returns NULL if all values in the column are NULL.
• SUM with DISTINCT counts each unique value only once: SUM(DISTINCT col)
• Use COALESCE(SUM(col), 0) to return 0 instead of NULL.
3. MIN( )
Returns the minimum (smallest) value in a column. Works on numeric, string, and date types.
Syntax
SELECT MIN(column_name) FROM table_name;
Examples
-- Earliest order date
SELECT MIN(order_date) AS first_order
FROM orders;
-- Cheapest product in each category
SELECT category, MIN(price) AS lowest_price
FROM products
GROUP BY category;
-- Minimum salary among employees hired after 2020
SELECT MIN(salary) AS min_salary
FROM employees
WHERE hire_date > '2020-01-01';
Key Notes
• For strings, MIN returns the alphabetically first value.
• For dates, MIN returns the earliest date.
• NULL values are excluded from comparison.
4. AVG( )
Returns the average (arithmetic mean) of a numeric column. NULL values are excluded from
both the numerator and denominator.
Page 2 of 5
SQL Aggregate Functions — Study Notes
Syntax
SELECT AVG(column_name) FROM table_name;
Examples
-- Average order value
SELECT AVG(amount) AS avg_order_value
FROM orders;
-- Average salary by department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
-- Customers with above-average spending
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (SELECT AVG(amount) FROM orders);
Key Notes
• AVG ignores NULLs — it does NOT treat them as zero.
• To include NULLs as zero: AVG(COALESCE(col, 0))
• Result is usually a decimal. Use ROUND(AVG(col), 2) for formatting.
• AVG(DISTINCT col) averages only unique values.
5. MAX( )
Returns the maximum (largest) value in a column. Like MIN, it works on numeric, string, and
date types.
Syntax
SELECT MAX(column_name) FROM table_name;
Examples
-- Most expensive product
SELECT MAX(price) AS highest_price
FROM products;
Page 3 of 5
SQL Aggregate Functions — Study Notes
-- Latest transaction date per user
SELECT user_id, MAX(transaction_date) AS last_activity
FROM transactions
GROUP BY user_id;
-- Employee with highest salary in each department
SELECT department, MAX(salary) AS top_salary
FROM employees
GROUP BY department;
Key Notes
• For strings, MAX returns the alphabetically last value.
• For dates, MAX returns the most recent date.
• Commonly used with GROUP BY to find the 'best' per group.
6. GROUP BY & HAVING
GROUP BY groups rows with the same value in specified columns. HAVING filters groups after
aggregation (like WHERE, but for aggregates).
GROUP BY Syntax
SELECT col, AGG_FUNC(col2)
FROM table
GROUP BY col;
HAVING Syntax
SELECT col, SUM(amount)
FROM table
GROUP BY col
HAVING SUM(amount) > 1000;
WHERE vs HAVING
Clause Filters When Applied Can Use Aggregates?
WHERE Individual rows Before GROUP BY No
HAVING Groups / aggregates After GROUP BY Yes
7. Combining All Functions
-- Sales summary report per product category
Page 4 of 5
SQL Aggregate Functions — Study Notes
SELECT
category,
COUNT(*) AS total_orders,
SUM(revenue) AS total_revenue,
AVG(revenue) AS avg_revenue,
MIN(revenue) AS min_revenue,
MAX(revenue) AS max_revenue
FROM sales
WHERE year = 2024
GROUP BY category
HAVING SUM(revenue) > 5000
ORDER BY total_revenue DESC;
8. Common Gotchas & Tips
Pitfall Problem Fix
NULL + number SUM returns NULL if any value is Use COALESCE(col, 0)
NULL without safeguard
AVG rounding AVG may return long decimals ROUND(AVG(col), 2)
COUNT(*) vs COUNT(col) skips NULLs, Choose intentionally
COUNT(col) COUNT(*) does not
HAVING without Treats entire table as one group Valid but rare use case
GROUP BY
Mixing aggregate & Non-aggregate column not in Add to GROUP BY or use aggregate
non-aggregate GROUP BY causes error
9. NULL Behavior Summary
All aggregate functions (except COUNT(*)) ignore NULL values:
• SUM(1, 2, NULL) → 3
• AVG(10, 20, NULL) → 15 (not 10)
• MIN(5, NULL, 3) → 3
• MAX(5, NULL, 3) → 5
• COUNT(col with NULLs) → counts only non-NULLs
• COUNT(*) → counts all rows including NULLs
— End of Notes —
Page 5 of 5