Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
SQL HAVING
The HAVING clause is used in SQL to filter groups of data that are created by the GROUP BY clause. While the WHERE clause filters individual rows before grouping, HAVING filters groups after aggregation. This is particularly useful when you need to apply conditions to aggregated data.
Purpose
- Filter Aggregated Data: Allows you to filter results based on aggregate functions like SUM(), COUNT(), AVG(), etc.
- Post-Aggregation Filtering: Applies conditions to groups after the GROUP BY clause has grouped the data.
Syntax
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
GROUP BY column_name(s)
HAVING aggregate_function(column_name) condition;
Example:
1. Find Departments with More than 10 Employees
Find Departments with More than 10 Employees
SELECT department_id, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 10;
Output:
department_id | num_employees |
1 | 15 |
2 | 12 |
2. Get Products with Total Sales Above 100,000
Get Products with Total Sales Above 100,000
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(sales_amount) > 100000;
Output:
product_id | total_sales |
102 | 200000 |
103 | 175000 |
3. Find Customers with More than 5 Orders
This query retrieves customers who have placed more than 5 orders.
SELECT customer_id, COUNT(order_id) AS num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;
Output:
customer_id | num_orders |
1 | 7 |
3 | 8 |
4. Get Departments with an Average Salary Greater than 40,000
This query finds departments where the average salary of employees is greater than 40,000.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 40000;
Output:
department_id | avg_salary |
1 | 45000 |
3 | 50000 |
Tips to Remember
- Order of Clauses: HAVING is used after GROUP BY and is applied to the aggregated results. It cannot be used without GROUP BY in a query.
- Aggregation: HAVING works with aggregate functions, allowing you to filter based on calculations like sums, averages, and counts.
- Condition Placement: Conditions in HAVING are applied to the results of aggregate functions, whereas conditions in WHERE are applied to individual rows before aggregation.
- Combine with ORDER BY: You can use HAVING in combination with ORDER BY to sort the results after filtering.
Power of HAVING
- Advanced Filtering: Provides a way to filter based on aggregated data, enabling complex queries and reporting.
- Detailed Analysis: Facilitates detailed analysis by allowing conditions on calculated data, making it easier to derive insights.
Frequently Asked Questions
Q1: Can I use HAVING without GROUP BY?
A1: Technically, you can use HAVING without GROUP BY if you include an aggregate function. However, it is most commonly used with GROUP BY to filter grouped results.
Example:
SELECT COUNT(employee_id) AS total_employees
FROM employees
HAVING COUNT(employee_id) > 50;
Q2: How does HAVING differ from WHERE?
A2: WHERE filters rows before any grouping and aggregation occur, while HAVING filters groups after aggregation has been applied.
Q3: Can I use multiple conditions in HAVING?
A3: Yes, you can use multiple conditions in the HAVING clause, combining them with AND or OR.
Example:
SELECT department_id, COUNT(employee_id) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5 AND AVG(salary) > 40000;
Q4: Can I use HAVING with non-aggregated columns?
A4: No, HAVING is specifically designed for use with aggregated data. For conditions on non-aggregated columns, use the WHERE clause.
Q5: Can HAVING be used with multiple aggregate functions?
A5: Yes, you can use multiple aggregate functions in the HAVING clause to filter based on various calculated metrics.
Example:
SELECT department_id, COUNT(employee_id) AS num_employees, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5 AND SUM(salary) > 200000;
