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;
┌─────────────┐
│ 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;
┌───────────────┐
│ product_count │
├───────────────┤
│ 7             │
└───────────────┘

Count with WHERE

How many Electronics sales?

SELECT COUNT(*) AS electronics_sales
FROM sales
WHERE category = 'Electronics';
┌────────────────────┐
│ electronics_sales  │
├────────────────────┤
│ 4                  │
└────────────────────┘

Count DISTINCT Values

How many unique categories exist?

SELECT COUNT(DISTINCT category) AS unique_categories
FROM sales;
┌────────────────────┐
│ 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;
┌───────────────┐
│ total_revenue │
├───────────────┤
│ 2295          │
└───────────────┘

Sum with WHERE

Total revenue from Furniture only.

SELECT SUM(amount) AS furniture_revenue
FROM sales
WHERE category = 'Furniture';
┌───────────────────┐
│ 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;
┌────────────────────┐
│ average_sale       │
├────────────────────┤
│ 327.857142857142…  │
└────────────────────┘

Rounded Average

Round to 2 decimal places for cleaner output.

SELECT ROUND(AVG(amount), 2) AS average_sale
FROM sales;
┌──────────────┐
│ 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;
┌─────────────┬──────────────┐
│ 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;
┌─────────────┬───────────────┬──────────┬──────────┬──────────┐
│ total_sales │ total_revenue │ avg_sale │ min_sale │ max_sale │
├─────────────┼───────────────┼──────────┼──────────┼──────────┤
│ 7           │ 2295          │ 327.8571 │ 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;
┌─────────────┬─────────────┐
│ category    │ sales_count │
├─────────────┼─────────────┤
│ Electronics │ 4           │
│ Furniture   │ 3           │
└─────────────┴─────────────┘

Revenue by Category

Total revenue per category.

SELECT 
    category,
    SUM(amount) AS total_revenue
FROM sales
GROUP BY category;
┌─────────────┬───────────────┐
│ category    │ total_revenue │
├─────────────┼───────────────┤
│ Electronics │ 1700          │
│ Furniture   │ 595           │
└─────────────┴───────────────┘

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;
┌─────────────┬───────────┬───────┬──────────┐
│ category    │ num_sales │ total │ avg_sale │
├─────────────┼───────────┼───────┼──────────┤
│ Electronics │ 4         │ 1700  │ 425.00   │
│ Furniture   │ 3         │ 595   │ 198.33   │
└─────────────┴───────────┴───────┴──────────┘
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;
┌─────────────┬───────────────┐
│ category    │ total_revenue │
├─────────────┼───────────────┤
│ Electronics │ 1700          │ ← Highest
│ 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;
┌─────────────┬───────────────┐
│ category    │ total_revenue │
├─────────────┼───────────────┤
│ Electronics │ 1700          │
└─────────────┴───────────────┘

Furniture (595) is excluded

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;
┌─────────────┬───────────┐
│ category    │ num_sales │
├─────────────┼───────────┤
│ Electronics │ 4         │
│ Furniture   │ 3         │
└─────────────┴───────────┘
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;
┌─────────────┬───────────┬──────────┐
│ category    │ num_sales │ avg_sale │
├─────────────┼───────────┼──────────┤
│ Electronics │ 3         │ 558.33   │
│ Furniture   │ 2         │ 275.00   │
└─────────────┴───────────┴──────────┘

Process:
1. WHERE amount > 50 (filters out Mouse $25, Lamp $45)
2. GROUP BY category
3. HAVING AVG(amount) > 150 (keeps both groups)
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;
┌─────────────┬────────────┬───────────────┐
│ 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;
┌─────────┬──────────────┬─────────────┐
│ name    │ total_orders │ total_spent │
├─────────┼──────────────┼─────────────┤
│ Alice   │ 3            │ 425         │
│ Bob     │ 1            │ 180         │
│ Charlie │ 0            │ NULL        │
└─────────┴──────────────┴─────────────┘

Note: LEFT JOIN includes Charlie (no orders)

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;
┌─────────────┬───────────┬───────────────┬──────────┬─────────────┬──────────────┐
│ category    │ num_sales │ total_revenue │ avg_sale │ lowest_sale │ highest_sale │
├─────────────┼───────────┼───────────────┼──────────┼─────────────┼──────────────┤
│ Electronics │ 4         │ 1700          │ 425.00   │ 25          │ 1200         │
│ Furniture   │ 3         │ 595           │ 198.33   │ 45          │ 350          │
└─────────────┴───────────┴───────────────┴──────────┴─────────────┴──────────────┘

This report shows:
- Only positive amounts (WHERE)
- Categories with 2+ sales (HAVING)
- Complete breakdown per category
- Sorted by revenue (highest first)

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
Database FundamentalsLesson 6