MySQL HAVING Clause

The HAVING clause was added to SQL because the WHEREkeyword cannot be used with aggregate functions. The HAVING clause filters records after the GROUP BY clause has been applied.

HAVING vs WHERE

Basic Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING condition
ORDER BY column1;

Examples

1. Basic HAVING Example

-- Departments with more than 3 employees
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

2. HAVING with AVG()

-- Departments with average salary greater than 50000
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

3. HAVING with SUM()

-- Products with total sales greater than 1000
SELECT product_id, SUM(quantity) as total_sold
FROM order_items
GROUP BY product_id
HAVING SUM(quantity) > 1000;

4. HAVING with Multiple Conditions

-- Departments with more than 2 employees AND average salary > 50000
SELECT department, 
       COUNT(*) as employee_count,
       AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 2 AND AVG(salary) > 50000;

5. WHERE and HAVING Together

-- First filter active employees, then filter groups
SELECT department, 
       COUNT(*) as active_employees,
       AVG(salary) as avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 1;

Real-World Scenarios

Scenario 1: E-commerce Analysis

-- Customers who have placed more than 5 orders
SELECT customer_id, 
       COUNT(order_id) as total_orders,
       SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

Scenario 2: Employee Performance

-- Employees with sales above target
SELECT employee_id, 
       SUM(sales_amount) as total_sales
FROM sales
WHERE YEAR(sale_date) = 2024
GROUP BY employee_id
HAVING SUM(sales_amount) > 100000;

Scenario 3: Product Analysis

-- Products sold more than 100 units last month
SELECT product_id, 
       SUM(quantity) as units_sold,
       AVG(price) as avg_price
FROM order_items
WHERE MONTH(order_date) = MONTH(CURRENT_DATE() - INTERVAL 1 MONTH)
GROUP BY product_id
HAVING SUM(quantity) > 100;

Common Mistakes

• Using WHERE instead of HAVING with aggregate functions
• Forgetting GROUP BY when using HAVING
• Using column aliases incorrectly in HAVING clause
• Applying HAVING before GROUP BY (wrong order)

Query Execution Order

1. FROM - Select tables
2. WHERE - Filter rows
3. GROUP BY - Group rows
4. HAVING - Filter groups
5. SELECT - Select columns
6. ORDER BY - Sort results
7. LIMIT - Limit rows