having clause in sql
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.
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
|The HAVING clause is utilised in conjunction|
|with the group by to filter the results.||The WHERE clause is utilised to filter the results and applies to individual rows.|
|The HAVING clause is utilised to specify a condition on the group.||The WHERE clause is utilised to specify a condition on individual rows.|
|The HAVING clause is utilised to filter the groups based on aggregate functions.||The WHERE clause is utilised to filter the rows based on individual values.|
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.
Answer: d. To filter rows after the result set is returned
Answer: a. A WHERE clause filters records before the result set is returned, while a HAVING clause filters after the result set is returned
Answer: b. Aggregate
Answer: b. GROUP BY clause
Related Tutorials to watch
Top Articles toRead