HAVING

The HAVING clause is similar to the WHERE clause with the exception that it is only used to filter after aggregation has occurred. In the previous sections, we looked at using GROUP BY, here we implement the same queries as before but we then add a HAVING clause for post aggregation selection

HAVING filter less than

The following query will compute the average profit by product category and return categories of product whose avg_profit is less than 6000

SELECT category,
    round(avg((price - cost)*stock_quantity),2) as avg_profit
FROM products
GROUP BY 1 
HAVING avg((price - cost)*stock_quantity) < 6000;

  category   | avg_profit
------------+------------
Electronics |    4919.59
Storage     |    5859.83
Auto        |    5699.81
Office      |    2697.83
Drones      |    5939.73
Kitchen     |    5008.87
(6 rows)

Notice that the HAVING clause is always placed after the GROUP BY because it filters the results of a computation.

HAVING filter greater than

Let's take a look at the opposite of the earlier query by reversing the condition

SELECT 
    category, 
    round(avg((price - cost)*stock_quantity), 2) as avg_profit 
FROM products 
GROUP BY 1 
HAVING avg((price - cost) * stock_quantity) > 6000;

  category    | avg_profit
--------------+------------
Audio         |    6186.00
Monitors      |    8839.48
Laptops       |    8799.83
Camera        |    6524.76
TV            |    8499.84
Accessories   |    7911.99
Home          |    7835.99
Gaming        |    6780.87
Personal Care |    6486.28
Health        |    6408.76
Smart Home    |    6543.11
Smartphones   |    7869.81
Tablets       |   13399.33
Wearables     |   11648.89
(14 rows)