COUNT, SUM, AVG, MIN, MAX
Aggregate functions compute a single result from multiple rows. COUNT counts rows. SUM adds values. AVG computes the mean. MIN/MAX find the extreme values. They are the foundation of all analytics and reporting in SQL.
Aggregate Function Reference
- COUNT(*) — count all rows including NULLs
- COUNT(col) — count non-NULL values in that column
- COUNT(DISTINCT col) — count unique non-NULL values
- SUM(col) — total of all non-NULL numeric values
- AVG(col) — mean of non-NULL values (SUM / COUNT)
- MIN(col) — smallest value (works on numbers, dates, text)
- MAX(col) — largest value (works on numbers, dates, text)
- All aggregates ignore NULL values except COUNT(*)
Aggregate Examples on ecommerce_db
-- How many orders do we have?
SELECT COUNT(*) AS total_orders FROM orders;
-- Result: 3
-- How many unique customers placed orders?
SELECT COUNT(DISTINCT user_id) AS active_customers FROM orders;
-- Result: 2
-- Total revenue from all payments
SELECT SUM(amount) AS total_revenue FROM payments;
-- Result: 1599.98
-- Average order value
SELECT ROUND(AVG(total_amount), 2) AS avg_order_value FROM orders;
-- Result: 563.32
-- Price range of products
SELECT
MIN(price) AS cheapest,
MAX(price) AS most_expensive,
AVG(price) AS average_price
FROM products;
-- cheapest: 89.99 | most_expensive: 999.99 | average_price: 563.32
-- Earliest and latest order
SELECT
MIN(created_at) AS first_order,
MAX(created_at) AS latest_order
FROM orders;Quick Quiz
Tip
Tip
Practice COUNT SUM AVG MIN MAX in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Eliminate redundancy — each fact stored once. 3NF covers most real-world needs.
Common Mistake
Warning
A common mistake with COUNT SUM AVG MIN MAX is skipping edge case testing — empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task — (1) Write a working example of COUNT SUM AVG MIN MAX from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Aggregate functions compute a single result from multiple rows.
- COUNT(*) — count all rows including NULLs
- COUNT(col) — count non-NULL values in that column
- COUNT(DISTINCT col) — count unique non-NULL values