Filtering Grouped Data with HAVING
In SQL database management systems, the HAVING clause is used to filter the output results of the GROUP BY clause table operation. The HAVING clause behaves similarly to the WHERE clause, with the main difference being that the WHERE clause filters table rows before table data grouping, while the HAVING clause filters table groups after the GROUP BY operation is applied. This allows database users to use the HAVING clause wherever needed. Database users can filter table data based on aggregate functions such as COUNT(), SUM(), AVG(), MIN(), or MAX() using the HAVING and GROUP BY clauses to manually filter data and information as needed.
Syntax of the HAVING clause in SQL databases.
The basic syntax for applying the HAVING clause with the GROUP BY clause in SQL database management systems is:
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING aggregate_function(column_name) condition;
HAVING clause elements.
- aggregate_function(column_name) – This is the aggregate function applied to the grouped table data in the existing database table.
- HAVING – This filter the data results of the GROUP BY clause based on the condition of the aggregate function applied by the user in the HAVING clause.
- condition – This is the aggregate function condition applied in the existing HAVING clause (e.g., SUM(salary) > 30000).
Using HAVING with aggregate functions in SQL databases.
The HAVING clause is commonly used in SQL database tables when the database user needs to filter table data result groups based on the result of a particular numeric aggregate function.
Example of HAVING with aggregate functions.
Filtering an SQL database using SUM(). If a database user wants to find departments in the existing employee table where the total employee salary is greater than 40000 rupees, the database user can apply the HAVING clause with the SUM() aggregate function.
SELECT department, SUM(salary) AS total_salary
FROM employe
GROUP BY department
HAVING SUM(salary) > 40000;
Result of HAVING with aggregate functions.
department total_salary
Marketing 74000
Development 44000
Design 63000
In this example.
- This table query groups and displays employees by their department.
- The HAVING clause filters out any department data from the entire employee table where the total employee salary (SUM(salary)) is less than or equal to 40000.
- Finally, it displays only those records where the employee salary is greater than 40000.
COUNT() function filtering example.
Filtering an SQL database using the COUNT() function.
If a database user wants to find departments in the existing employee table with more than 2 employees, the database user can use the HAVING clause with the COUNT() function.
SELECT department, COUNT(*) AS num_employe
FROM employe
GROUP BY department
HAVING COUNT(*) > 2;
Result of COUNT() function filtering.
department num_employe
Marketing 4
Development 7
Design 9
In this example.
- This example query groups employees according to their department.
- The HAVING clause filters out department records from the existing table that have 2 or fewer employees. The result shows only departments with more than 2 employees, including Marketing, Development, and Design departments.
AVG() function filtering example. Filtering an SQL database using the AVG() function.
If the database user wants to find departments in the existing employee table where the average employee salary is greater than 70,000 rupees, they can use the HAVING clause with the AVG() aggregate function.
SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
HAVING AVG(salary) > 70000;
Result of AVG() function filtering.
department avg_salary
Marketing 74000
In this example.
- This table query groups employees by their department.
- The HAVING clause filters out departments in the existing employee table where the average employee salary is less than or equal to 70,000.
- In the result, only the Marketing department fulfils this condition.
Using HAVING with multiple conditions in an SQL database.
Database users can add and apply multiple conditions in the HAVING clause, just as they do in the WHERE clause.
Example of HAVING with multiple conditions in an SQL database.
Here, the database user needs to find departments in the employee table where the total employee salary is greater than 30,000 and the average salary is greater than 40,000.
SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
HAVING SUM(salary) > 30000 AND AVG(salary) > 40000;
SQL database result of HAVING with multiple conditions.
department total_salary avg_salary
Marketing 74000 148,000
Development 44000 88,000
Design 63000 126,000
In this example.
- This database query groups employees according to their department.
- Here, the HAVING clause filters only those departments in the employee table where the total employee salary is less than 30000, or the average salary is less than 40000.
- Here, only the Marketing, Development, and Design departments fulfil both these conditions.
Difference between WHERE and HAVING in SQL database management systems.
In SQL database tables, the WHERE clause filters table rows before grouping. The WHERE clause is used to filter individual table rows based on table column values.
On the other hand, the HAVING clause filters table groups after grouping. The HAVING clause is mostly used to filter database table record data based on numeric aggregate functions.
Example of the difference between WHERE and HAVING.
Here, let’s assume the database user wants to filter table record data in the employee table based on their salary and group them according to their department type.
In this example, using the WHERE clause to filter employee records with a salary greater than 70,000 rupees.
SELECT department, COUNT(*) AS num_employe
FROM employe
WHERE salary > 70000
GROUP BY department;
Here, in this example, using the HAVING clause to filter employee departments based on the total employee salary. SELECT department, SUM(salary) AS total_salary
FROM employee
GROUP BY department
HAVING SUM(salary) > 400000;
Here, in the first example query, the WHERE clause filters the employee table records before grouping, selecting only those with a salary greater than 70,000. In the second example query, the HAVING clause filters the table records after the total salary is calculated for each employee department.
Sorting Grouped Data in SQL Database Tables with ORDER BY.
Database users can apply the ORDER BY clause to sort the grouped table data results in a particular order, either ascending or descending. The ORDER BY clause can be used in conjunction with the HAVING clause to first filter and then order the database results.
Example of Sorting Grouped Data.
Here, the database user creates a list of employee departments with a total salary greater than 55000, sorted in descending order by total salary.
SELECT department, SUM(salary) AS total_salary
FROM employee
GROUP BY department
HAVING SUM(salary) > 55000
ORDER BY total_salary DESC;
Result of Sorting Grouped Data.
department total_salary
Marketing 74000
Design 63000
In this example.
- Here, in this example, the HAVING clause filters the employee table department records where the total employee salary is greater than 55000 rupees.
- The ORDER BY clause then sorts and displays the table results in descending order based on the total employee salary.
Conclusion of Filtering Grouped Data with HAVING. In SQL database management systems.
- the HAVING clause is used to filter groups of table rows after the GROUP BY table operation. The HAVING clause is typically used with numeric aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().
- Similarly, in SQL database management systems, the WHERE clause filters table rows before grouping the table data, while the HAVING clause filters and previews the table results after grouping.
- Database users can use the HAVING clause in SQL database management systems to apply multiple conditions and filter table data based on the results of numeric aggregate functions.
- Database users can apply HAVING in conjunction with GROUP BY and ORDER BY to group, filter, and sort table data in a particular order.
