PostgreSQL Having

The PostgreSQL HAVING clause is a powerful tool that allows you to filter and aggregate data in a query based on conditions applied to the result of a GROUP BY clause. While the WHERE clause is used to filter rows before the grouping takes place, the HAVING clause is applied after the grouping and allows you to filter the groups themselves.

Syntax

The syntax for using the HAVING clause in PostgreSQL is as follows:

SELECT column1, column2, ...
FROM table
GROUP BY column1, column2, ...
HAVING condition;

Here, the SELECT statement specifies the columns you want to retrieve from the table. The GROUP BY clause defines the grouping criteria, which can include one or more columns. The HAVING clause applies conditions to the groups generated by the GROUP BY clause.

The conditions specified in the HAVING clause can include aggregate functions such as SUM, AVG, MIN, MAX, COUNT, etc. This allows you to filter the groups based on their aggregated values. For example, you can use the HAVING clause to retrieve groups with a total sales amount greater than a certain threshold or groups with a minimum value higher than a specific value.

Example

Let’s look at an example to better understand how the HAVING clause works. Consider a table named orders with columns product, category, and price. We can use the following query to retrieve categories that have a total sales amount greater than $10,000:

SELECT category, SUM(price) AS total_sales
FROM orders
GROUP BY category
HAVING SUM(price) > 10000;

In this example, the GROUP BY clause groups the rows by the category column. The SUM(price) function calculates the total sales amount for each category. The HAVING clause filters the groups, retaining only those with a total sales amount greater than $10,000. The result will include the category and the corresponding total sales for each qualifying group.

Having example

Goods table

SELECT good_type, count(good_type) g_count
FROM goods
GROUP BY good_type
HAVING count(good_type) > 1

Result

It’s important to note that the HAVING clause operates on the aggregated result set and only retains groups that meet the specified conditions. If you want to filter individual rows before the grouping, you should use the WHERE clause instead.

In summary, the HAVING clause in PostgreSQL allows you to filter grouped data based on conditions applied to the aggregated values. It is a valuable tool for performing complex queries and extracting meaningful information from your data sets.