The Project | About Us | Contribute | Donations | License

HOME

The HAVING clause in SQL is used to filter records that work on aggregated data. It is similar to the WHERE clause but is used for groups of rows rather than individual rows. The HAVING clause is often used in conjunction with the GROUP BY clause to filter groups of rows that meet certain conditions.

Key Concepts

Purpose: The HAVING clause is used to filter groups of rows created by the GROUP BY clause.
Difference from WHERE: The WHERE clause filters rows before any groupings are made, while the HAVING clause filters groups after the GROUP BY clause has been applied.
Syntax: The HAVING clause is placed after the GROUP BY clause and before the ORDER BY clause (if used).

Syntax

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

AGGREGATE_FUNCTION can be functions like COUNT(), SUM(), AVG(), MIN(), MAX().
aggregate_condition is the condition applied to the aggregated data.

Practical Example

Consider a table sales with the following structure:

sale_id	product_id	quantity	price
1	101	2	50
2	102	1	30
3	101	3	50
4	103	5	20
5	102	2	30

Example Query

Let's say we want to find products that have been sold more than 3 times in total.

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 3;

Explanation

SELECT: We select the product_id and the sum of quantity as total_quantity.
FROM: The data is taken from the sales table.
GROUP BY: We group the results by product_id.
HAVING: We filter the groups to include only those where the total quantity sold is greater than 3.

Result

product_id	total_quantity
101	5
103	5

Exercises

Exercise 1

Given the following employees table:

employee_id	department	salary
1	HR	5000
2	IT	6000
3	HR	4500
4	IT	7000
5	Sales	4000

Write a query to find departments with an average salary greater than 5000.

Solution

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

Result

department	avg_salary
IT	6500

Exercise 2

Given the following orders table:

order_id	customer_id	amount
1	201	150
2	202	200
3	201	100
4	203	250
5	202	300

Write a query to find customers who have placed orders totaling more than 300.

Solution

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 300;

Result

customer_id	total_amount
202	500

Common Mistakes

Using HAVING without GROUP BY: The HAVING clause is intended to filter groups of rows, so it should be used with GROUP BY.
Confusing WHERE and HAVING: Remember that WHERE filters rows before grouping, while HAVING filters groups after grouping.

Conclusion

The HAVING clause is a powerful tool for filtering aggregated data in SQL. By understanding its purpose and how it differs from the WHERE clause, you can effectively use it to perform complex data analysis. Practice using the HAVING clause with different aggregate functions and conditions to become proficient in filtering grouped data.

HAVING Clause

Key Concepts

Syntax

Practical Example

Example Query

Explanation

Result

Exercises

Exercise 1

Solution

Result

Exercise 2

Solution

Result

Common Mistakes

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project