Understanding PostgreSQL Having Clause
The PostgreSQL HAVING Clause is a SQL feature whereby you can filter the results after the data is grouped using the GROUP BY clause. In most cases, it is connected to aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN(). But the PostgreSQL HAVING Clause grants you also the ability to implement predicate conditions to filter groups based on the results of these functions and or other conditions imposed on the aggregated data.
The present part comprises a comprehensive exposition of the PostgreSQL HAVING Clause, which will also include its syntax, usage, and examples. This section reveals how the PostgreSQL HAVING Clause supports the operation of WHERE clause by which it is possible to make the group aggregate operators filtration work after the aggregate.
1. What is the PostgreSQL HAVING Clause?
- The PostgreSQL HAVING clause is a part that is used to select the required data from the initial list after the GROUP BY operation has been completed.
- HAVING clause is a syntax that is commonly attached to aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to put restrictions on the results found.
2. Syntax of the PostgreSQL HAVING Clause
- The essential syntax of the PostgreSQL HAVING clause goes as follows.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING aggregate_condition;
- Here’s what each part of the query is about:
- column_name: The column(s) by which you want to group your data.
- aggregate_function(column_name): The aggregate function applied to a column, like COUNT(), SUM(), AVG(), etc.
- WHERE condition: Optional; filters the rows before grouping.
- GROUP BY column_name: Groups the data based on the specified column.
- HAVING aggregate_condition: Filters the groups based on the condition applied to aggregated values.
3. How Does the HAVING Clause Differ from WHERE?
- Essentially, WHERE and HAVING already constitute the differences in their filtering fields:
- WHERE is placed before GROUP BY to sort and aggregate rows.
- HAVING does it after the grouping. (i.e., after GROUP BY has been performed).
- Let’s go through an example to clear the idea of the concepts:
- Example with WHERE:
SELECT department, COUNT(*)
FROM the employees
WHERE salary > 50000
GROUP BY department;
- In this case, WHERE salary>50000 filter out rows before the group by department, that is, only employees with salary greater than 50,000 are counted in the COUNT() of each department.
- Example with HAVING:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
- In this case, the HAVING COUNT(*) > 10 condition filters the grouped results, which tell us that only departments with over 10 employees are included, notwithstanding the fact that all employees are taken into account before the grouping process.
4. Using Aggregate Functions with HAVING
- Aggregate functions are the functions that calculate one value of a number.
- HAVING clause is most often used with aggregate functions.
- Here are some common examples:
- COUNT(): Provides the number of rows or elements.
- SUM(): Adds the values together.
- AVG(): Determines the mean of values.
- MAX(): Provides the biggest value.
- MIN(): Produces the smallest value.
- We’ll now consider the situation where we utilize HAVING clause coupled with these aggregate functions.
Example with COUNT:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
The purpose of the query is finding the amount of employees in each department and then excluding the departments that have less than 6 employees from the result set.
Example with SUM:
SELECT department, SUM(earn)
FROM employees
GROUP BY department
HAVING SUM(salary) > 1000000;
In this scenario, the employees are listed according to their department and their respective sums of earnings. With the help of the HAVING clause, only those departments whose total salary is greater than 1 million are listed in the output.
Example with AVG:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
In this query, we group employees by department and calculate the average salary in each department. The HAVING clause restricts the outcomes, keeping just the departments where the average salary is above 60,000.
5. Combining HAVING with Multiple Aggregate Conditions
- The “HAVING” clause in PostgreSQL enables you to employ multiple conditions.
- You can utilize and join up different aggregate functions in the same “HAVING” clause through the logical operators such as AND, OR and NOT.
Multiple Conditions
SELECT department, AVG(salary), COUNT(*)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 AND COUNT(*) > 10;
The SQL query provided aims to filter out employees from the departments that pay an average salary greater than 50,000 and have more than 10 employees.
6. Using HAVING without GROUP BY
- Although the HAVING clause is normally used together with the GROUP BY statement, it can also be used singly.
- In this situation, HAVING acts like a WHERE clause which selects the entire data set on the basis of aggregated values.
Example without GROUP BY:
SELECT SUM(salary)
FROM employees
HAVING SUM(salary) > 1000000;
This statement will produce the sum of all the salaries but only when the overall sum exceeds 1 million.
7. Performance Considerations with HAVING
- The use of the HAVING clause to restrict the data after the GROUP BY operation may result in performance degradation when large datasets are involved.
- It is generally more efficient to construct conditions that can be implemented in WHERE prior to the computation of another operation, thus keeping the data that needs to be processed to a minimum.
- Take this as an example: If you want to apply the condition for grouping the rows first, use the WHERE clause to filter out data from the table query.
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 10;
- The use of WHERE clause salary > 50000 makes the HAVING and GROUP BY operations suffer from less traffic thus bringing the query performance to a better level.
8. Examples of Practical Use Cases for HAVING
Case 1: Identifying Departments with Large Numbers of Employees
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 20;
In the following case, we are looking for departments that have more than 20 employees. The HAVING clause restricts the outcomes after the count of employees has been displayed division-wise.
Case 2: Finding Departments with High Total Salary Expenses
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 1000000;
We decompose the total salary expenses over departments where the expenses are over 1 million. After grouping by department, we impose a filter with the HAVING clause to select only those rows whose sum of salaries is greater than the specified value.
Case 3: Finding Departments with High Average Salaries and Large Employee Count
SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000 AND COUNT(*) > 10;
This query is used in order to recognize those departments in which the average salary exceeds 70,000 and the number of employees is higher than 10.
Case 4: Using HAVING with Multiple Aggregate Functions
SELECT department, COUNT(*) AS num_employees, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 AND SUM(salary) > 500000;
In this case, we only consider departments that have more than 5 employees and the resulting salary exceeds 500,000.
9. Advanced: Using Subqueries with HAVING
- Also you can bring subqueries in the HAVING clause to filter the fine points of other aggregate queries.
Subquery in HAVING:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > (SELECT AVG(employee_count) FROM (SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department) AS subquery);
We are practically checking the count of employees in the department and the average number across all of the employees with the named subquery in the HAVING clause.