Preamble
PostgreSQL HAVING statement is used in combination with GROUP BY statement to limit groups of returned strings only to those whose condition is TRUE.
The syntax for the HAVING statement in PostgreSQL
SELECT expression1_id, expression2_id,. expression_n_id,
aggregate_function
FROM tabs
[WHERE conds]
GROUP BY expression1_id, expression2_id,... expression_n_id
HAVING cond;
Statement parameters and arguments
- aggregate_function – This can be an aggregate function such as sum, count, min, max, or avg.
- expression1_id, expression_2id,… expression_n_id – Expressions which are not contained in the aggregate function and must be included in the GROUP BY operator.
- WHERE conds – Optional. These are conditions for selecting records.
- HAVING cond – This is another condition applied only to aggregate results to limit groups of returned lines. Only those groups whose condition is evaluated as TRUE will be included in the result set.
Example using the sum function
Let’s consider a HAVING example that uses the sum function.
You can also use the sum function to return a department and the sum(salary) function to that department. The PostgreSQL statement HAVING will filter the results so that only departments with a total salary greater than 25000 USD will be returned.
SELECT depart, sum(salary) AS "Salaries for the department"
FROM empls
GROUP BY depart
HAVING sum(salary) > 25000;
Example using count function
Let’s see how to use the HAVING operator with the count function.
You can use the count function to return a department and the number of employees (for that department) that have “Active” status. The PostgreSQL statement HAVING will filter the results so that only departments with a maximum of 35 employees will be returned.
SELECT department, count(*) AS "Number of employees"
FROM empls
WHERE status = 'Active'
GROUP BY depart
HAVING count(*) <= 35;
Example using min function
Let’s now see how to use the HAVING operator with the min function.
You can also use the min function to return the name of each department and the minimum wage in that department. PostgreSQL operator HAVING returns only those departments where the minimum wage is less than 36000 USD.
SELECT depart, min(salary) AS "Lowest salary".
FROM empls
GROUP BY depart
HAVING min(salary) < 36000;
Example using max function
Finally, let’s consider how to use the HAVING operator with the max function.
For example, you can also use the max function to return the name of each department and the maximum salary of the department. PostgreSQL statement HAVING will return only those departments whose maximum salary is more or equal to 31950 USD.
SELECT depart, max(salary) AS "Highest salary"
FROM empls
GROUP BY depart
HAVING max(salary) >= 31950;
PostgreSQL: Group By Having | Course
About Enteros
Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Database Performance with Enteros and AWS Resource Groups: A RevOps Approach to Streamlined Efficiency
- 13 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enhancing Healthcare Data Integrity: How Enteros, Logical Models, and Database Security Transform Healthcare Operations
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…