fogserv.cloud fogserv.cloud

SQL Aggregation — Summarizing Data with COUNT, SUM, AVG, GROUP BY, and Window Functions

Status: Active | Last Updated: 2026-08-29 Category: SQL — Aggregation Prerequisites: SQL fundamentals, SQL Joins Tags: sql, aggregation, count, sum, avg, min, max, group-by, having, subquery, window-functions, row-number, rank Estimated Time: 4-5 hours (Self-paced, includes lab time)

Summary

Aggregation functions turn many rows into a single summary value: COUNT(*), SUM(amount), AVG(price), MIN(), MAX(). Combined with GROUP BY, they answer business questions like "how many orders per customer?" and "what is the average revenue by region?" Window functions extend this further, computing per-row aggregates without collapsing rows — enabling rankings, running totals, and trend analysis.

What You'll Learn (Core Competencies)

Table of Contents

  1. Architectural Overview & Core Schema
  2. Deep Dive & Implementation
  3. Anti-Patterns & Common Pitfalls
  4. Independent Challenge
  5. Consolidation & Key Invariants
  6. Next Steps

1. Architectural Overview & Core Schema

What Aggregation Does

Raw SQL returns rows. Aggregation collapses rows into summary values. The distinction:

Raw rows (transactional view):
  order_id | user_id | amount
  ---------|---------|-------
  1        | 101     | 50.00
  2        | 101     | 75.00
  3        | 102     | 30.00

Aggregated (analytical view — one row per user):
  user_id | order_count | total_spent
  --------|-------------|------------
  101     | 2           | 125.00
  102     | 1           | 30.00

Aggregation is the bridge between transactional data (day-to-day operations) and analytical data (business decisions).

Aggregation and NULL

Schema Used in This Lesson

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    user_id    INTEGER REFERENCES users(id),
    product    TEXT    NOT NULL,
    amount     REAL    NOT NULL,
    status     TEXT    DEFAULT 'pending',
    created_at TEXT    DEFAULT (date('now'))
);

2. Deep Dive & Implementation

Aggregate Functions

The five core aggregates:

SELECT
    COUNT(*)              AS total_orders,      -- count all rows
    COUNT(DISTINCT user_id) AS unique_customers, -- count distinct values
    SUM(amount)           AS total_revenue,
    AVG(amount)           AS average_order_value,
    MIN(amount)           AS smallest_order,
    MAX(amount)           AS largest_order
FROM orders;

COUNT(DISTINCT col) is especially useful for "how many distinct X did we see?"

GROUP BY

Group rows by one or more columns, then compute aggregates per group:

SELECT
    user_id,
    COUNT(*)    AS order_count,
    SUM(amount) AS total_spent,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;

GROUP BY user_id creates one result row per unique user_id. Every column in SELECT must either be in GROUP BY or be wrapped in an aggregate function — this is enforced in PostgreSQL and MySQL; SQLite is lenient but should not be relied upon.

Multiple grouping columns:

-- Revenue by product and month
SELECT
    product,
    strftime('%Y-%m', created_at) AS month,
    COUNT(*)    AS units_sold,
    SUM(amount) AS revenue
FROM orders
GROUP BY product, strftime('%Y-%m', created_at)
ORDER BY revenue DESC;

HAVING vs WHERE

SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'          -- only completed orders (pre-aggregation filter)
GROUP BY user_id
HAVING COUNT(*) > 5                 -- only users with >5 orders (post-aggregation filter)
ORDER BY total_spent DESC;

Rule of thumb: if your filter mentions an aggregate, use HAVING. Otherwise, use WHERE.

Subqueries for Complex Aggregation

When a single GROUP BY can't solve the problem, subqueries help:

-- Find users whose total spending is above the average
SELECT
    user_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > (
    SELECT AVG(total_per_user)
    FROM (SELECT SUM(amount) AS total_per_user
          FROM orders
          GROUP BY user_id)
);

The inner subquery computes per-user totals; the outer query filters above-average spenders.

Window Functions

Window functions compute an aggregate over a window of rows relative to the current row — without collapsing rows like GROUP BY does.

Syntax: aggregate_or_function() OVER (PARTITION BY col ORDER BY col ROWS BETWEEN ...)

-- Revenue with running total and rank per user
SELECT
    id,
    user_id,
    amount,
    SUM(amount)    OVER (PARTITION BY user_id ORDER BY created_at
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                         AS running_total,
    ROW_NUMBER()   OVER (PARTITION BY user_id ORDER BY amount DESC)
                         AS order_rank,
    RANK()         OVER (PARTITION BY user_id ORDER BY amount DESC)
                         AS dense_rank,
    LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at)
                         AS prev_order_amount,
    LEAD(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at)
                         AS next_order_amount
FROM orders
ORDER BY user_id, created_at;

Guided Checkpoint

Verify GROUP BY aggregation and HAVING/WHERE distinction using SQLite:

sqlite3 :memory: -header -column "
  CREATE TABLE orders (id INTEGER, user_id INTEGER, amount REAL, status TEXT);
  INSERT INTO orders VALUES (1, 1, 100, 'completed'), (2, 1, 200, 'completed');
  INSERT INTO orders VALUES (3, 1,  50, 'pending'),  (4, 2,  75, 'completed');
  INSERT INTO orders VALUES (5, 2,  80, 'pending');

  -- Test 1: GROUP BY — per-user totals
  SELECT '=== Test 1: GROUP BY user_id ===' AS test;
  SELECT user_id, COUNT(*) AS orders, SUM(amount) AS total
  FROM orders GROUP BY user_id;

  -- Test 2: WHERE (pre-filter) vs HAVING (post-filter)
  SELECT '=== Test 2: WHERE status=completed ===' AS test;
  SELECT user_id, COUNT(*) AS orders
  FROM orders WHERE status = 'completed' GROUP BY user_id;

  SELECT '=== Test 3: HAVING COUNT(*) > 1 ===' AS test;
  SELECT user_id, COUNT(*) AS orders
  FROM orders GROUP BY user_id HAVING COUNT(*) > 1;

  -- Test 4: Window function — running total per user
  SELECT '=== Test 4: SUM() OVER (running total) ===' AS test;
  SELECT id, user_id, amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY id) AS running_total
  FROM orders ORDER BY user_id, id;
"

Expected: Test 1 shows user 1 = 3 orders / 350 total, user 2 = 2 orders / 155 total. Test 2 drops pending orders. Test 3 shows only user 1 (3 orders > 1). Test 4 shows incremental running totals per user.

3. Anti-Patterns & Common Pitfalls

Anti-Pattern 1: Selecting Non-Aggregated Columns Without GROUP BY

-- Fails or gives unpredictable results in strict mode (PostgreSQL/MySQL)
SELECT id, user_id, SUM(amount) FROM orders;

-- Correct: include id in GROUP BY (if each id is unique) or remove it
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

Anti-Pattern 2: Using WHERE to Filter on Aggregate Results

-- Invalid in all SQL dialects: WHERE cannot reference SUM()
SELECT user_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 100   -- ERROR
GROUP BY user_id;

-- Correct: use HAVING
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 100;

Anti-Pattern 3: N+1 Aggregation (Multiple Queries Instead of One)

-- Bad: N queries for N users
-- for each user: SELECT SUM(amount) FROM orders WHERE user_id = :id

-- Good: one query
SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id;

Anti-Pattern 4: Missing Indexes on GROUP BY and JOIN Columns

If GROUP BY user_id is slow on a large orders table, an index on user_id (especially as a foreign key) dramatically speeds up the aggregation.

4. Independent Challenge

Build analytical queries for an e-commerce dataset.

Schema:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL);
CREATE TABLE orders   (id INTEGER PRIMARY KEY, product_id INTEGER REFERENCES products(id),
                       quantity INTEGER, amount REAL, order_date TEXT);

Write queries that:

  1. Total revenue and order count per product category (sorted by revenue DESC).
  2. Find the top-3 best-selling products by quantity (not revenue).
  3. Calculate month-over-month revenue growth using LAG() (window function). Show month, revenue, prev_month_revenue, and growth_pct.
  4. Identify products that have never been ordered (subquery or LEFT JOIN with NULL check).
  5. For each category, find the product with the highest average order amount.

Save as challenge_ecommerce_analytics.sql. Test against a seeded database with at least 20 rows.

5. Consolidation & Key Invariants

Bullet-list summary of the must-remember rules.

6. Next Steps

Change Log

Choose Theme

Your selection is saved locally.

Neural Cacophony
Aperture v2
Flux v1
Mosaic Chaos
Nexus v1
Nexus Zest
Prism v2
Synapse