TECH **SQL for Data Analysis: Aggregate Functions & GROUP BY – Zero-Fluff Study Guide**

SQL for Data Analysis: Aggregate Functions & GROUP BY – Zero-Fluff Study Guide

1. What This Is & Why It Matters

You’re analyzing sales data for an e-commerce company. Your boss asks: - "How many orders did we get last month?" - "What’s the average order value by product category?" - "Which region has the highest sales?"

Without aggregate functions (COUNT, SUM, AVG, MIN, MAX) and GROUP BY, you’d be stuck writing dozens of individual queries—one for each product, region, or time period. Worse, you’d miss patterns (e.g., "Why is Category X underperforming in Europe?").

Why this matters in production:
- Performance: Aggregates reduce millions of rows into a few meaningful numbers. Without them, you’d pull raw data into Python/R, slowing down analysis.
- Decision-making: Executives don’t care about individual rows—they want trends (e.g., "Revenue is up 12% YoY").
- Debugging: If a dashboard shows "Total Sales = $0," you’ll use SUM and GROUP BY to trace where the data broke.

Real-world scenario:
You inherit a legacy SQL query that calculates "daily active users" by counting rows in a logins table. It runs in 30 seconds—too slow for a real-time dashboard. You rewrite it with COUNT(DISTINCT user_id) and GROUP BY date, cutting runtime to 2 seconds.

2. Core Concepts & Components

? COUNT()

• Definition: Counts rows or non-NULL values.
• Production insight: COUNT(*) counts all rows (including NULLs), while COUNT(column) ignores NULLs. If your user_id column has NULLs, COUNT(user_id) ≠ COUNT(*).

? SUM()

• Definition: Adds up numeric values.
• Production insight: If you SUM a column with NULLs, they’re treated as 0. Use COALESCE(column, 0) to avoid surprises.

? AVG()

• Definition: Calculates the arithmetic mean.
• Production insight: AVG ignores NULLs. If 50% of your data is NULL, the average will be skewed. Use AVG(COALESCE(column, 0)) if you want to include NULLs as 0.

? MIN() / MAX()

• Definition: Finds the smallest/largest value in a column.
• Production insight: Works on dates, strings, and numbers. MAX(date) = most recent order; MIN(name) = first alphabetically.

? GROUP BY

• Definition: Splits data into groups and applies aggregates to each.
• Production insight: If you forget GROUP BY, you’ll get one row with aggregates for the entire table (usually not what you want).

? HAVING (vs. WHERE)

• Definition: Filters groups after aggregation (WHERE filters before).
• Production insight: HAVING is slower than WHERE—use WHERE first to reduce data.

? DISTINCT (Bonus)

• Definition: Removes duplicates.
• Production insight: COUNT(DISTINCT user_id) is 10x slower than COUNT(*). Use sparingly.

3. Step-by-Step Hands-On: Analyzing E-Commerce Sales

Prerequisites

• A SQL environment (PostgreSQL, MySQL, BigQuery, or SQLite).
• A sample dataset (we’ll use this schema):

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    product_id INT,
    category VARCHAR(50),
    region VARCHAR(50),
    amount DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO orders VALUES
(1, 101, '2023-01-15', 1001, 'Electronics', 'North', 199.99),
(2, 102, '2023-01-16', 1002, 'Clothing', 'South', 49.99),
(3, 101, '2023-01-17', 1003, 'Electronics', 'North', 299.99),
(4, 103, '2023-01-18', 1004, 'Furniture', 'East', 599.99),
(5, 104, '2023-01-19', 1002, 'Clothing', 'West', 39.99),
(6, 101, '2023-01-20', 1005, 'Electronics', 'North', 99.99);

Task 1: Basic Aggregates

Goal: Calculate total sales, average order value, and count of orders.

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order_value,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders;

Expected Output:

total_orders | total_sales | avg_order_value | smallest_order | largest_order
-------------+-------------+-----------------+----------------+--------------
6            | 1289.94     | 214.99          | 39.99          | 599.99

Task 2: GROUP BY Category

Goal: Break down sales by product category.

SELECT
    category,
    COUNT(*) AS order_count,
    SUM(amount) AS category_sales,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY category;

Expected Output:

category    | order_count | category_sales | avg_order_value
------------+-------------+----------------+----------------
Electronics | 3           | 599.97         | 199.99
Clothing    | 2           | 89.98          | 44.99
Furniture   | 1           | 599.99         | 599.99

Task 3: GROUP BY + HAVING

Goal: Find categories with more than 2 orders.

SELECT
    category,
    COUNT(*) AS order_count
FROM orders
GROUP BY category
HAVING COUNT(*) > 2;

Expected Output:

category    | order_count
------------+-------------
Electronics | 3

Task 4: GROUP BY Multiple Columns

Goal: Sales by region and category.

SELECT
    region,
    category,
    SUM(amount) AS sales
FROM orders
GROUP BY region, category
ORDER BY region, sales DESC;

Expected Output:

region | category    | sales
-------+-------------+-------
East   | Furniture   | 599.99
North  | Electronics | 599.97
South  | Clothing    | 49.99
West   | Clothing    | 39.99

Task 5: COUNT(DISTINCT)

Goal: Count unique customers per region.

SELECT
    region,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY region;

Expected Output:

region | unique_customers
-------+-----------------
North  | 1
South  | 1
East   | 1
West   | 1

4. ? Production-Ready Best Practices

✅ Performance

• Filter first: Use WHERE to reduce data before aggregating.
  ```sql -- ❌ Slow: Aggregates all rows, then filters SELECT category, SUM(amount) FROM orders GROUP BY category HAVING SUM(amount) > 100;

-- ✅ Fast: Filters first SELECT category, SUM(amount) FROM orders WHERE amount > 0 -- Filter early! GROUP BY category; `` - AvoidSELECT *withGROUP BY: Only select columns inGROUP BYor aggregates.
- IndexGROUP BYcolumns: Speeds up sorting (e.g.,CREATE INDEX idx_category ON orders(category)`).

✅ Accuracy

• Handle NULLs: Use COALESCE or ISNULL to avoid skewed results.
  ```sql -- ❌ NULLs ignored in AVG SELECT AVG(amount) FROM orders;

-- ✅ Treat NULLs as 0 SELECT AVG(COALESCE(amount, 0)) FROM orders; `` - Round decimals:ROUND(SUM(amount), 2)` to avoid floating-point weirdness.

✅ Readability

• Alias aggregates: SUM(amount) AS total_sales > SUM(amount).
• Order results: ORDER BY sales DESC for top categories.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. "What’s the difference between WHERE and HAVING?"
2. WHERE filters rows before aggregation.

3. HAVING filters groups after aggregation.

4. "How do you count unique customers?"

5. COUNT(DISTINCT customer_id).

6. "Why does AVG(column) return NULL?"

7. All values in the group are NULL.

8. "What’s wrong with this query?"
   sql
SELECT category, SUM(amount), customer_id
FROM orders
GROUP BY category;

9. Error: customer_id isn’t in GROUP BY or aggregated.

⚠️ Trap Distinctions

• COUNT(*) vs COUNT(column): The first counts rows, the second counts non-NULL values.
• GROUP BY vs ORDER BY: GROUP BY creates groups; ORDER BY sorts results.
• SUM vs AVG: SUM adds values; AVG divides by count.

7. ? Hands-On Challenge

Challenge:
Find the top 3 regions by total sales, but only include regions with more than 1 order.

Solution:

SELECT
    region,
    SUM(amount) AS total_sales
FROM orders
GROUP BY region
HAVING COUNT(*) > 1
ORDER BY total_sales DESC
LIMIT 3;

Why it works:
- GROUP BY region creates groups.
- HAVING COUNT(*) > 1 filters regions with >1 order.
- ORDER BY total_sales DESC sorts by sales.
- LIMIT 3 returns only the top 3.

8. ? Rapid-Reference Crib Sheet

⚠️ Exam Traps:
- GROUP BY must include all non-aggregated columns in SELECT.
- HAVING is slower than WHERE—filter early! - AVG ignores NULLs (unlike SUM).

9. ? Where to Go Next

1. PostgreSQL Aggregate Functions Docs
2. SQLZoo GROUP BY Tutorial
3. Book: SQL for Data Analysis by O’Reilly (Chapter 3: Aggregating Data).
4. Practice: LeetCode SQL Problems (Filter by "GROUP BY").