GROUP BY and HAVING

GROUP BY Clause

The GROUP BY clause groups rows that have the same values into summary rows. It's used with aggregate functions to perform calculations on each group.

Basic Syntax

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1;

Sample Data

CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    category VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10,2),
    region VARCHAR(20)
);

INSERT INTO sales (product_id, category, sale_date, amount, region)
VALUES
    (1, 'Electronics', '2024-01-15', 500.00, 'North'),
    (2, 'Electronics', '2024-01-16', 300.00, 'South'),
    (3, 'Clothing', '2024-01-17', 150.00, 'North'),
    (4, 'Clothing', '2024-01-18', 200.00, 'East'),
    (5, 'Electronics', '2024-01-19', 750.00, 'West'),
    (6, 'Clothing', '2024-01-20', 100.00, 'North'),
    (7, 'Electronics', '2024-01-21', 400.00, 'South');

GROUP BY Examples

-- Total sales per category
SELECT 
    category,
    SUM(amount) AS total_sales,
    COUNT(*) AS sale_count
FROM sales
GROUP BY category;

-- Average sale amount per region
SELECT 
    region,
    AVG(amount) AS avg_sale,
    COUNT(*) AS num_sales
FROM sales
GROUP BY region;

-- Sales statistics per product
SELECT 
    product_id,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_sale,
    MIN(amount) AS min_sale,
    MAX(amount) AS max_sale
FROM sales
GROUP BY product_id;

GROUP BY with Multiple Columns

-- Group by category and region
SELECT 
    category,
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category, region;

-- Group by product and date
SELECT 
    product_id,
    sale_date,
    SUM(amount) AS daily_sales
FROM sales
GROUP BY product_id, sale_date;

GROUP BY Best Practices

1. Include all non-aggregated columns in GROUP BY
2. Use meaningful column aliases for aggregates
3. Order by aggregate results for readability
4. Be consistent with column names

-- Good: All non-aggregated columns in GROUP BY
SELECT 
    category,
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category, region
ORDER BY total_sales DESC;

-- Bad: Missing column in GROUP BY (error in strict SQL mode)
SELECT 
    category,
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category;  -- ERROR: region not in GROUP BY

GROUP BY is essential for summarizing data and creating aggregated reports.

GROUP BY Multiple Columns

Grouping by multiple columns creates groups for each unique combination of values. This provides more detailed aggregations.

Multiple Column Examples

-- Sales by category and region
SELECT 
    category,
    region,
    SUM(amount) AS total_sales,
    COUNT(*) AS num_sales
FROM sales
GROUP BY category, region
ORDER BY category, total_sales DESC;

-- Sales by product and month
SELECT 
    product_id,
    DATE_FORMAT(sale_date, '%Y-%m') AS month,
    SUM(amount) AS monthly_sales
FROM sales
GROUP BY product_id, DATE_FORMAT(sale_date, '%Y-%m');

-- Detailed breakdown
SELECT 
    category,
    region,
    DATE_FORMAT(sale_date, '%Y-%m') AS month,
    SUM(amount) AS total,
    AVG(amount) AS average
FROM sales
GROUP BY category, region, DATE_FORMAT(sale_date, '%Y-%m');

Ordering GROUP BY Results

-- Order by group columns
SELECT category, region, SUM(amount)
FROM sales
GROUP BY category, region
ORDER BY category ASC, region ASC;

-- Order by aggregate result
SELECT category, region, SUM(amount) AS total
FROM sales
GROUP BY category, region
ORDER BY total DESC;

-- Order by multiple aggregates
SELECT category, region, SUM(amount) AS total, COUNT(*) AS count
FROM sales
GROUP BY category, region
ORDER BY total DESC, count DESC;

GROUP BY with Expressions

-- Group by calculated column
SELECT 
    YEAR(sale_date) AS sale_year,
    MONTH(sale_date) AS sale_month,
    SUM(amount) AS monthly_total
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date);

-- Group by date truncation
SELECT 
    DATE_TRUNC('week', sale_date) AS week_start,
    SUM(amount) AS weekly_total
FROM sales
GROUP BY DATE_TRUNC('week', sale_date);

-- Group by value ranges
SELECT 
    CASE
        WHEN amount < 200 THEN 'Low'
        WHEN amount < 500 THEN 'Medium'
        ELSE 'High'
    END AS sale_range,
    COUNT(*) AS count
FROM sales
GROUP BY 
    CASE
        WHEN amount < 200 THEN 'Low'
        WHEN amount < 500 THEN 'Medium'
        ELSE 'High'
    END;

GROUP BY with NULLs

-- NULLs are grouped together
SELECT 
    category,
    COUNT(*) AS count
FROM sales
GROUP BY category;
-- NULL category values form their own group

-- Filter NULL groups
SELECT 
    category,
    COUNT(*) AS count
FROM sales
WHERE category IS NOT NULL
GROUP BY category;

GROUP BY Best Practices

1. Use column aliases in ORDER BY for clarity
2. Group by the least number of columns needed
3. Consider performance - more columns = more groups
4. Use meaningful names for aggregated columns

-- Good: Clear and efficient
SELECT 
    category AS product_category,
    region AS sales_region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category, region
ORDER BY total_sales DESC;

Multiple column grouping provides detailed insights into your data.

HAVING Clause

The HAVING clause filters groups after they have been created by GROUP BY. It's used with aggregate functions to filter grouped results.

Basic Syntax

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1
HAVING condition;

HAVING Examples

-- Categories with total sales > 500
SELECT 
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 500;

-- Regions with more than 2 sales
SELECT 
    region,
    COUNT(*) AS sale_count
FROM sales
GROUP BY region
HAVING COUNT(*) > 2;

-- Products with average sale > 300
SELECT 
    product_id,
    AVG(amount) AS avg_sale
FROM sales
GROUP BY product_id
HAVING AVG(amount) > 300;

-- Multiple HAVING conditions
SELECT 
    category,
    region,
    SUM(amount) AS total_sales,
    COUNT(*) AS num_sales
FROM sales
GROUP BY category, region
HAVING SUM(amount) > 200 AND COUNT(*) >= 2;

HAVING vs WHERE

-- WHERE filters rows BEFORE grouping
SELECT 
    category,
    SUM(amount) AS total_sales
FROM sales
WHERE amount > 100  -- Filters individual rows
GROUP BY category;

-- HAVING filters groups AFTER grouping
SELECT 
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 500;  -- Filters grouped results

-- Both together
SELECT 
    category,
    SUM(amount) AS total_sales
FROM sales
WHERE amount > 100  -- Filter rows first
GROUP BY category
HAVING SUM(amount) > 500;  -- Then filter groups

HAVING with Aggregate Functions

-- Using different aggregates in HAVING
SELECT 
    category,
    COUNT(*) AS count,
    SUM(amount) AS total,
    AVG(amount) AS average
FROM sales
GROUP BY category
HAVING COUNT(*) >= 2 AND AVG(amount) > 200;

-- Using MAX and MIN
SELECT 
    region,
    MAX(amount) AS max_sale,
    MIN(amount) AS min_sale
FROM sales
GROUP BY region
HAVING MAX(amount) - MIN(amount) > 400;

HAVING Best Practices

1. Use HAVING for aggregate conditions
2. Use WHERE for row-level conditions
3. Combine both when needed
4. Alias aggregates for readability

-- Good: Clear separation of concerns
SELECT 
    category,
    COUNT(*) AS num_sales,
    SUM(amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01'  -- Row filter
GROUP BY category
HAVING total_sales > 1000;  -- Group filter

-- Also good: Use column reference in HAVING (MySQL, PostgreSQL)
SELECT 
    category,
    COUNT(*) AS num_sales,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING total_sales > 1000;

HAVING is essential for filtering aggregated results and creating meaningful summaries.

WHERE vs HAVING

Understanding the difference between WHERE and HAVING is crucial for writing correct SQL queries.

Key Differences

Feature	WHERE	HAVING
Timing	Before grouping	After grouping
Purpose	Filter individual rows	Filter groups
Aggregate Functions	Cannot use	Can use
Columns	Any column	Only grouped columns or aggregates
Performance	Faster (reduces rows early)	Slower (processes all groups)

Execution Order

-- SQL execution order:
-- 1. FROM
-- 2. WHERE (filters rows)
-- 3. GROUP BY (creates groups)
-- 4. HAVING (filters groups)
-- 5. SELECT (returns columns)
-- 6. ORDER BY (sorts results)

-- Example:
SELECT 
    category,
    SUM(amount) AS total
FROM sales           -- 1. From sales table
WHERE amount > 100   -- 2. Filter rows where amount > 100
GROUP BY category    -- 3. Group by category
HAVING total > 500   -- 4. Filter groups where total > 500
ORDER BY total DESC; -- 5. Sort by total descending

When to Use WHERE

-- Use WHERE for:
-- 1. Filtering before aggregation
SELECT category, SUM(amount)
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY category;

-- 2. Filtering on non-aggregated columns
SELECT category, COUNT(*)
FROM sales
WHERE region = 'North'
GROUP BY category;

-- 3. Performance optimization
SELECT category, SUM(amount)
FROM sales
WHERE amount > 100  -- Reduces rows before grouping
GROUP BY category;

When to Use HAVING

-- Use HAVING for:
-- 1. Filtering on aggregate results
SELECT category, SUM(amount)
FROM sales
GROUP BY category
HAVING SUM(amount) > 500;

-- 2. Filtering on grouped columns with aggregates
SELECT category, COUNT(*), AVG(amount)
FROM sales
GROUP BY category
HAVING COUNT(*) >= 3 AND AVG(amount) > 200;

-- 3. Complex conditions on groups
SELECT region, SUM(amount)
FROM sales
GROUP BY region
HAVING SUM(amount) > (SELECT AVG(amount) FROM sales);

Common Mistakes

-- Mistake: Using WHERE with aggregate
SELECT category, SUM(amount)
FROM sales
WHERE SUM(amount) > 500  -- ERROR!
GROUP BY category;

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

-- Mistake: Using HAVING before GROUP BY
SELECT category, SUM(amount)
FROM sales
HAVING SUM(amount) > 500  -- ERROR!
GROUP BY category;

-- Correct: GROUP BY before HAVING
SELECT category, SUM(amount)
FROM sales
GROUP BY category
HAVING SUM(amount) > 500;

Performance Tips

-- Good: Filter early with WHERE
SELECT category, SUM(amount)
FROM sales
WHERE sale_date >= '2024-01-01'  -- Reduces data early
  AND region = 'North'
GROUP BY category
HAVING SUM(amount) > 500;

-- Bad: Filter late with HAVING only
SELECT category, SUM(amount)
FROM sales
GROUP BY category
HAVING SUM(amount) > 500
  AND MIN(sale_date) >= '2024-01-01'  -- Slower
  AND COUNT(CASE WHEN region = 'North' THEN 1 END) > 0;

Summary

1. WHERE filters rows before grouping
2. HAVING filters groups after grouping
3. WHERE cannot use aggregate functions
4. HAVING can use aggregate functions
5. Use both for optimal performance

Understanding WHEN to use WHERE vs HAVING is fundamental to writing efficient and correct SQL queries.