Aggregations Count Sum Avg Min Max

Why this matters

As a Business Analyst, you will answer questions like: How many orders did we get this month? What was the total revenue? What is the average order value? The core SQL aggregations COUNT, SUM, AVG, MIN, and MAX let you summarize raw tables into business-ready numbers quickly and reliably.

• Daily metrics: count sign-ups, orders, tickets.
• Revenue tracking: sum of payments and refunds.
• Quality checks: min/max dates, outlier values.

Who this is for

Business Analysts, aspiring analysts, product owners, and anyone who needs to turn transactional data into insights without complex modeling.

Prerequisites

• Basic SELECT and FROM.
• Simple filtering with WHERE.
• Comfort reading column names and understanding NULL (missing) values.

Concept explained simply

Aggregations collapse many rows into summarized values. Think of them as calculators that look down a column and return one number.

Syntax essentials

• COUNT(*): counts all rows.
• COUNT(column): counts non-NULL values in column.
• SUM(column), AVG(column), MIN(column), MAX(column): ignore NULLs.
• Aliases: name your results for clarity using AS.
• Filtering rows before aggregation: WHERE applies to individual rows.
• Filtering groups after aggregation: HAVING applies to aggregated results.
• Distinct values: use DISTINCT inside an aggregate when needed, e.g., COUNT(DISTINCT customer_id).

-- One number across the whole table
SELECT COUNT(*) AS total_orders
FROM sales_orders;

-- Per group summaries
SELECT customer_id,
       COUNT(*) AS orders,
       SUM(total_amount) AS revenue
FROM sales_orders
WHERE status = 'Completed'
GROUP BY customer_id;

-- Filter aggregated results (groups) with HAVING
SELECT customer_id,
       COUNT(*) AS orders
FROM sales_orders
GROUP BY customer_id
HAVING COUNT(*) >= 5; 

Worked examples

Assume a table sales_orders(order_id, customer_id, status, total_amount, order_date).

1) Count total and completed orders

SELECT COUNT(*) AS total_orders,
       COUNT(CASE WHEN status = 'Completed' THEN 1 END) AS completed_orders
FROM sales_orders; 

Why: Quickly gauge activity and successful completions.

2) Revenue and average order value in May 2024

SELECT SUM(total_amount) AS total_revenue,
       AVG(total_amount) AS avg_order_value
FROM sales_orders
WHERE status = 'Completed'
  AND order_date >= DATE '2024-05-01'
  AND order_date < DATE '2024-06-01'; 

Why: Standard monthly reporting task.

3) First and latest order date, min/max order size

SELECT MIN(order_date) AS first_order_date,
       MAX(order_date) AS latest_order_date,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM sales_orders
WHERE status = 'Completed'; 

Why: Validate data ranges and detect outliers.

4) Average order value per customer (with decimal safety)

SELECT customer_id,
       CAST(SUM(total_amount) AS DECIMAL(18,2))
       / NULLIF(COUNT(*), 0) AS avg_order_value
FROM sales_orders
WHERE status = 'Completed'
GROUP BY customer_id; 

Why: Some systems perform integer division. Casting prevents truncation.

Practice: hands-on exercises

Use the same table: sales_orders(order_id, customer_id, status, total_amount, order_date).

• Exercise 1: Return a single row with total_rows, non_null_amounts, and completed_orders.
• Exercise 2: For each month in 2024, show month (YYYY-MM), total_revenue, avg_order_value for Completed orders; sort by month.
• Exercise 3: Return earliest_order_date, latest_order_date, min_amount, max_amount for Completed orders.

Common mistakes and self-check

• Mistake: Using COUNT(column) expecting to count all rows. Fix: Use COUNT(*) if you want to include NULLs.
• Mistake: Averaging integers via SUM(int)/COUNT(*) and getting truncated results. Fix: CAST to decimal or use AVG on a decimal cast.
• Mistake: Filtering aggregated results with WHERE. Fix: Use WHERE for row-level filters, HAVING for group-level filters.
• Mistake: Forgetting DISTINCT when counting unique customers. Fix: COUNT(DISTINCT customer_id) when you need unique counts.

Practical projects

• Daily KPI snapshot: total orders, completed orders, total revenue, average order value, min/max order date.
• Customer summary: per customer orders, revenue, average order value, first and latest order date.
• Monthly revenue board: per month total revenue and average order value with a top-5 largest order note using MAX.

Learning path

1. Master COUNT, SUM, AVG, MIN, MAX with simple WHERE filters.
2. Add GROUP BY for per-customer and per-month summaries.
3. Use HAVING to filter groups (e.g., customers with 5+ orders).
4. Combine with JOINs to bring in product or customer attributes.

Next steps

• Refactor your queries to ensure clear aliases and safe division.
• Add DISTINCT where unique counts are needed.
• Move on to GROUP BY with multiple columns and window functions next.

Mini challenge

Return, for 2024 Completed orders, the customer_id with the largest single order and include that order amount and date. Hint: Combine MAX with a subquery or use ORDER BY and LIMIT if supported.

-- Get the max order amount per customer in 2024
WITH max_per_cust AS (
  SELECT customer_id, MAX(total_amount) AS max_amt
  FROM sales_orders
  WHERE status = 'Completed'
    AND order_date >= DATE '2024-01-01'
    AND order_date < DATE '2025-01-01'
  GROUP BY customer_id
)
SELECT s.customer_id, s.total_amount AS largest_order_amount, s.order_date
FROM sales_orders s
JOIN max_per_cust m
  ON s.customer_id = m.customer_id
 AND s.total_amount = m.max_amt
WHERE s.status = 'Completed'
  AND s.order_date >= DATE '2024-01-01'
  AND s.order_date < DATE '2025-01-01'
ORDER BY largest_order_amount DESC; 

Quick test

Take the quick test to check your understanding. Available to everyone; only logged-in users get saved progress.