Aggregations & Grouping

COUNT, SUM, AVG, and GROUP BY.

Analyzing Data with Aggregates

Aggregate functions perform calculations across multiple rows and return a single result. Instead of retrieving individual records, you can answer questions like "What's the total?", "What's the average?", or "How many are there?". Combined with GROUP BY, aggregates let you analyze data by categories, the foundation of business intelligence and reporting.

Sample Data: sales

We'll analyze this sales table throughout the lesson:

┌──────────┬────────────┬─────────────┬────────┬──────────┐
│ sale_id  │ product    │ category    │ amount │ quantity │
├──────────┼────────────┼─────────────┼────────┼──────────┤
│ 1        │ Laptop     │ Electronics │ 1200   │ 1        │
│ 2        │ Mouse      │ Electronics │ 25     │ 3        │
│ 3        │ Desk       │ Furniture   │ 350    │ 1        │
│ 4        │ Chair      │ Furniture   │ 200    │ 2        │
│ 5        │ Monitor    │ Electronics │ 400    │ 2        │
│ 6        │ Lamp       │ Furniture   │ 45     │ 1        │
│ 7        │ Keyboard   │ Electronics │ 75     │ 2        │
└──────────┴────────────┴─────────────┴────────┴──────────┘

COUNT: Counting Rows

COUNT returns the number of rows that match your criteria.

Count All Rows

How many sales do we have?

SELECT COUNT(*) AS total_sales
FROM sales;

Expected Output:

┌─────────────┐
│ total_sales │
├─────────────┤
│           7 │
└─────────────┘

Count Specific Column

Count non-NULL values in a column (same as COUNT(*) if no NULLs exist).

SELECT COUNT(product) AS product_count
FROM sales;

Expected Output:

┌───────────────┐
│ product_count │
├───────────────┤
│             7 │
└───────────────┘

Count with WHERE

How many Electronics sales?

SELECT COUNT(*) AS electronics_sales
FROM sales
WHERE category = 'Electronics';

Expected Output:

┌───────────────────┐
│ electronics_sales │
├───────────────────┤
│                 4 │
└───────────────────┘

Count DISTINCT Values

How many unique categories exist?

SELECT COUNT(DISTINCT category) AS unique_categories
FROM sales;

Expected Output:

┌───────────────────┐
│ unique_categories │
├───────────────────┤
│                 2 │
└───────────────────┘

SUM: Adding Values

SUM adds up numeric values in a column.

Total Revenue

What's the total sales amount?

SELECT SUM(amount) AS total_revenue
FROM sales;

Expected Output:

┌───────────────┐
│ total_revenue │
├───────────────┤
│          2295 │
└───────────────┘

Sum with WHERE

Total revenue from Furniture only.

SELECT SUM(amount) AS furniture_revenue
FROM sales
WHERE category = 'Furniture';

Expected Output:

┌───────────────────┐
│ furniture_revenue │
├───────────────────┤
│               595 │
└───────────────────┘

AVG: Average Values

AVG calculates the mean (average) of numeric values.

Average Sale Amount

What's the average transaction value?

SELECT AVG(amount) AS average_sale
FROM sales;

Expected Output:

┌──────────────────────┐
│     average_sale     │
├──────────────────────┤
│ 327.8571428571428571 │
└──────────────────────┘

Rounded Average

Round to 2 decimal places for cleaner output.

SELECT ROUND(AVG(amount), 2) AS average_sale
FROM sales;

Expected Output:

┌──────────────┐
│ average_sale │
├──────────────┤
│       327.86 │
└──────────────┘

MIN & MAX: Finding Extremes

MIN and MAX find the smallest and largest values.

Lowest and Highest Sale

SELECT 
    MIN(amount) AS lowest_sale,
    MAX(amount) AS highest_sale
FROM sales;

Expected Output:

┌─────────────┬──────────────┐
│ lowest_sale │ highest_sale │
├─────────────┼──────────────┤
│          25 │         1200 │
└─────────────┴──────────────┘

Note: MIN and MAX also work with dates and strings (alphabetically).

Combining Multiple Aggregates

You can use multiple aggregate functions in one query.

Complete Sales Summary

SELECT 
    COUNT(*) AS total_sales,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_sale,
    MIN(amount) AS min_sale,
    MAX(amount) AS max_sale
FROM sales;

Expected Output:

┌─────────────┬───────────────┬──────────────────────┬──────────┬──────────┐
│ total_sales │ total_revenue │       avg_sale       │ min_sale │ max_sale │
├─────────────┼───────────────┼──────────────────────┼──────────┼──────────┤
│           7 │          2295 │ 327.8571428571428571 │       25 │     1200 │
└─────────────┴───────────────┴──────────────────────┴──────────┴──────────┘

GROUP BY: Aggregating by Category

GROUP BY splits data into groups and calculates aggregates for each group separately. This is where aggregates become truly powerful.

Sales Count by Category

How many sales in each category?

SELECT 
    category,
    COUNT(*) AS sales_count
FROM sales
GROUP BY category;

Expected Output:

┌─────────────┬─────────────┐
│  category   │ sales_count │
├─────────────┼─────────────┤
│ Furniture   │           3 │
│ Electronics │           4 │
└─────────────┴─────────────┘

Revenue by Category

Total revenue per category.

SELECT 
    category,
    SUM(amount) AS total_revenue
FROM sales
GROUP BY category;

Expected Output:

┌─────────────┬───────────────┐
│  category   │ total_revenue │
├─────────────┼───────────────┤
│ Furniture   │           595 │
│ Electronics │          1700 │
└─────────────┴───────────────┘

Multiple Aggregates with GROUP BY

Complete breakdown per category.

SELECT 
    category,
    COUNT(*) AS num_sales,
    SUM(amount) AS total,
    AVG(amount) AS avg_sale
FROM sales
GROUP BY category;

Expected Output:

┌─────────────┬───────────┬───────┬──────────────────────┐
│  category   │ num_sales │ total │       avg_sale       │
├─────────────┼───────────┼───────┼──────────────────────┤
│ Furniture   │         3 │   595 │ 198.3333333333333333 │
│ Electronics │         4 │  1700 │ 425.0000000000000000 │
└─────────────┴───────────┴───────┴──────────────────────┘

How GROUP BY Works

1. Splits rows into groups based on GROUP BY column(s)
2. Calculates aggregate function for each group separately
3. Returns one row per group

Sorting Grouped Results

Combine GROUP BY with ORDER BY to sort the aggregated results.

Sort by Revenue (Highest First)

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

Expected Output:

┌─────────────┬───────────────┐
│  category   │ total_revenue │
├─────────────┼───────────────┤
│ Electronics │          1700 │
│ Furniture   │           595 │
└─────────────┴───────────────┘

HAVING: Filtering Grouped Results

WHERE filters rows before grouping. HAVING filters groups after aggregation. Use HAVING when you need to filter based on aggregate results.

Categories with High Revenue

Show only categories with total revenue over $600.

SELECT 
    category,
    SUM(amount) AS total_revenue
FROM sales
GROUP BY category
HAVING SUM(amount) > 600;

Expected Output:

┌─────────────┬───────────────┐
│  category   │ total_revenue │
├─────────────┼───────────────┤
│ Electronics │          1700 │
└─────────────┴───────────────┘

Categories with Multiple Sales

Show categories with more than 2 sales.

SELECT 
    category,
    COUNT(*) AS num_sales
FROM sales
GROUP BY category
HAVING COUNT(*) > 2;

Expected Output:

┌─────────────┬───────────┐
│  category   │ num_sales │
├─────────────┼───────────┤
│ Furniture   │         3 │
│ Electronics │         4 │
└─────────────┴───────────┘

WHERE vs HAVING

WHERE: Filters individual rows before grouping
HAVING: Filters groups after aggregation
Can use both: WHERE first, then GROUP BY, then HAVING

Combining WHERE, GROUP BY, and HAVING

Use all three together for powerful filtering and analysis.

Complex Example

Find categories (excluding low-value items) with average sale over $150.

SELECT 
    category,
    COUNT(*) AS num_sales,
    AVG(amount) AS avg_sale
FROM sales
WHERE amount > 50
GROUP BY category
HAVING AVG(amount) > 150;

Expected Output:

┌─────────────┬───────────┬──────────────────────┐
│  category   │ num_sales │       avg_sale       │
├─────────────┼───────────┼──────────────────────┤
│ Furniture   │         2 │ 275.0000000000000000 │
│ Electronics │         3 │ 558.3333333333333333 │
└─────────────┴───────────┴──────────────────────┘

Query Execution Order:
1. FROM    - Get table
2. WHERE   - Filter rows (amount > 50)
3. GROUP BY- Create groups
4. HAVING  - Filter groups (AVG > 150)
5. SELECT  - Calculate aggregates
6. ORDER BY- Sort results (if present)

GROUP BY Multiple Columns

You can group by multiple columns to create more detailed breakdowns.

Extended sales table with dates:

┌──────────┬─────────────┬────────────┬────────┐
│ sale_id  │ category    │ sale_date  │ amount │
├──────────┼─────────────┼────────────┼────────┤
│ 1        │ Electronics │ 2024-01-15 │ 1200   │
│ 2        │ Electronics │ 2024-01-15 │ 25     │
│ 3        │ Furniture   │ 2024-01-15 │ 350    │
│ 4        │ Furniture   │ 2024-01-16 │ 200    │
│ 5        │ Electronics │ 2024-01-16 │ 400    │
└──────────┴─────────────┴────────────┴────────┘

Group by Category AND Date

Daily revenue per category.

SELECT 
    category,
    sale_date,
    SUM(amount) AS daily_revenue
FROM sales
GROUP BY category, sale_date
ORDER BY sale_date, category;

Expected Output:

┌─────────────┬────────────┬───────────────┐
│  category   │ sale_date  │ daily_revenue │
├─────────────┼────────────┼───────────────┤
│ Electronics │ 2024-01-15 │          1225 │
│ Furniture   │ 2024-01-15 │           350 │
│ Electronics │ 2024-01-16 │           400 │
│ Furniture   │ 2024-01-16 │           200 │
└─────────────┴────────────┴───────────────┘

Aggregates with JOINs

Combine aggregates with joins for powerful multi-table analysis.

Two tables:

customers:
┌─────────────┬────────┐
│ customer_id │ name   │
├─────────────┼────────┤
│ 1           │ Alice  │
│ 2           │ Bob    │
│ 3           │ Charlie│
└─────────────┴────────┘

orders:
┌──────────┬─────────────┬────────┐
│ order_id │ customer_id │ amount │
├──────────┼─────────────┼────────┤
│ 101      │ 1           │ 250    │
│ 102      │ 2           │ 180    │
│ 103      │ 1           │ 100    │
│ 104      │ 1           │ 75     │
└──────────┴─────────────┴────────┘

Customer Order Summary

Total orders and revenue per customer.

SELECT 
    c.name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

Expected Output:

┌─────────┬──────────────┬─────────────┐
│  name   │ total_orders │ total_spent │
├─────────┼──────────────┼─────────────┤
│ Charlie │            0 │             │
│ Alice   │            3 │         425 │
│ Bob     │            1 │         180 │
└─────────┴──────────────┴─────────────┘

Look at the order. We asked for ORDER BY total_spent DESC, yet Charlie is on top with no value at all. Charlie has no orders, so SUM returned NULL, and PostgreSQL sorts NULLs first in descending order (last in ascending). A NULL is not zero, it is unknown, so it cannot be ranked against numbers. Two fixes: wrap the sum in COALESCE(SUM(o.amount), 0) so the group really is zero, or spell out the placement with ORDER BY total_spent DESC NULLS LAST.

Also note COUNT. Charlie's total_orders is 0, not NULL.COUNT(o.order_id) counts non-NULL values, and Charlie's single NULL-padded row contributes nothing, giving 0. Had we writtenCOUNT(*) it would have counted that padded row and reported 1, which is the classic way to overcount customers who ordered nothing.

Common Mistakes

❌ Selecting Non-Grouped Columns

-- WRONG: product isn't in GROUP BY
SELECT category, product, SUM(amount)
FROM sales
GROUP BY category;

-- CORRECT: Only group columns or aggregates
SELECT category, SUM(amount)
FROM sales
GROUP BY category;

❌ Using WHERE Instead of HAVING

-- WRONG: Can't use aggregate in WHERE
SELECT category, SUM(amount)
FROM sales
WHERE SUM(amount) > 500
GROUP BY category;

-- CORRECT: Use HAVING for aggregates
SELECT category, SUM(amount)
FROM sales
GROUP BY category
HAVING SUM(amount) > 500;

❌ Forgetting GROUP BY with Aggregates

-- WRONG: Mixing aggregate and non-aggregate
SELECT category, SUM(amount)
FROM sales;

-- CORRECT: Group by non-aggregate columns
SELECT category, SUM(amount)
FROM sales
GROUP BY category;

Practical Example: Sales Report

Complete sales analysis with all concepts combined.

SELECT 
    category,
    COUNT(*) AS num_sales,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_sale,
    MIN(amount) AS lowest_sale,
    MAX(amount) AS highest_sale
FROM sales
WHERE amount > 0
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY total_revenue DESC;

Expected Output:

┌─────────────┬───────────┬───────────────┬──────────────────────┬─────────────┬──────────────┐
│  category   │ num_sales │ total_revenue │       avg_sale       │ lowest_sale │ highest_sale │
├─────────────┼───────────┼───────────────┼──────────────────────┼─────────────┼──────────────┤
│ Electronics │         4 │          1700 │ 425.0000000000000000 │          25 │         1200 │
│ Furniture   │         3 │           595 │ 198.3333333333333333 │          45 │          350 │
└─────────────┴───────────┴───────────────┴──────────────────────┴─────────────┴──────────────┘

Key Takeaways

COUNT counts rows (use COUNT(*) or COUNT(column))
SUM adds up numeric values
AVG calculates the mean/average
MIN/MAX find smallest/largest values
GROUP BY splits data into groups for separate aggregation
HAVING filters groups (use after GROUP BY)
WHERE filters rows before grouping
Execution order: WHERE → GROUP BY → HAVING → SELECT → ORDER BY
Aggregates with GROUP BY are the foundation of business reporting and analytics, they turn raw data into actionable insights

Back to "Joins & Relationships"

Database FundamentalsLesson 6

Next: "Database Design"