HAVING CLAUSE in SQL

Overview

The SQL HAVING clause is combined with GROUP BY clause to restrict the groups of returned rows whose condition is TRUE. It is used to filter records that one or more columns have grouped. The HAVING clause is used instead of the WHERE clause with aggregate functions.

HAVING Syntax

Rishwanth's company recently implemented a new SQL database for product inventory tracking. They needed to track the number of items in each warehouse and wanted to make sure that no inventory was duplicated. To ensure this, they added a HAVING clause to their SQL query to filter out duplicate inventory numbers. Unfortunately, he doesn't know what to do. Let's help him with it.

The HAVING clause is combined with the GROUP BY clause to restrict the groups of returned rows to only those whose condition is TRUE.

Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

This syntax can be used to select columns from a table, filter the rows based on a given condition, group the rows by one or more columns, and restrict the groups based on a given condition. Once the results are grouped, the ORDER BY clause can be used to sort the results.

SQL HAVING Example

Table Name: customers

SELECT COUNT(*) AS num_customers, city
FROM customers
GROUP BY city
HAVING COUNT(*) > 10;

This statement would return the number of customers and the city for any city where the number of customers is greater than 10.

More HAVING Examples

Table Name: Employees

SELECT s.Department, AVG(s.Salary)
FROM Employees s
GROUP BY s.Department
HAVING AVG(s.Salary) > 10000

This query is used to find the average salaries of departments in the Employees table where the average salary is greater than $10,000. The query uses a GROUP BY clause to group the results by department and then uses a HAVING clause to filter out the results with an average salary lower than $10,000.

Difference between HAVING and WHERE clause

Conclusion

Rishwanth's company implemented a new SQL database for product inventory tracking and added a HAVING clause to their SQL query to filter out duplicate inventory numbers. This allowed their database to track each item accurately and ensure their inventory was represented. By adding this clause, they could ensure that no inventory was duplicated and accurately track the number of items in each warehouse.

Key takeaways

1. A HAVING clause filters records in a Group BY clause compared to a WHERE clause in a SELECT statement.
2. The HAVING clause limits the groups based on a certain condition.
3. The HAVING clause can be utilized with aggregate functions such as Count, Sum, AVG, MAX, MIN, etc.
4. The HAVING clause isn't utilized with single-row functions.
5. The HAVING clause can be utilized in combination with the Group BY clause.
6. The HAVING clause is executed after the Group BY clause is executed.
7. The HAVING clause can be used with multiple conditions using the AND and OR operators.

Quiz

1. What is the purpose of a having clause in SQL? 
   1. To restrict the number of rows returned 
   2. To limit the number of columns returned 
   3. To group rows together 
   4. To filter rows after the result set is returned

Answer: d. To filter rows after the result set is returned

2. What is the difference between a WHERE clause and a HAVING clause?
   1. A WHERE clause filters records before the result set is returned, while a HAVING clause filters after the result set is returned 
   2. A WHERE clause filters records after the result set is returned, while a HAVING clause filters before the result set is returned 
   3. A WHERE clause can be used with aggregates, while a HAVING clause cannot 
   4. A WHERE clause is used to filter records, while a HAVING clause is used to group records

Answer: a. A WHERE clause filters records before the result set is returned, while a HAVING clause filters after the result set is returned

3. What type of condition must be used in a HAVING clause? 
   1. Boolean  
   2. Aggregate 
   3. Logical 
   4. Comparison

Answer: b. Aggregate

4. What type of clause is a HAVING clause? 
   1. SELECT clause 
   2. GROUP BY clause 
   3. WHERE clause 
   4. ORDER BY clause

Answer: b. GROUP BY clause