PostgreSQL HAVING Clause
HAVING
TheHAVING clause was added to SQL because theWHERE clause cannot be used with aggregate functions.
Aggregate functions are often used withGROUP BY clauses,and by addingHAVING we can write condition like we do withWHERE clauses.
Example
List only countries that are represented more than 5 times:
SELECT COUNT(customer_id), country
FROM customers
GROUP BY country
HAVING COUNT(customer_id) > 5;
Run Example »FROM customers
GROUP BY country
HAVING COUNT(customer_id) > 5;
More HAVING Examples
The following SQL statement lists only orders with a total price of 400$ or more:
Example
SELECT order_id, SUM(order_details.quantity * products.price)
FROM order_details
LEFT JOIN products on products.product_id = order_details.product_id
GROUP BY order_id
HAVING SUM(order_details.quantity * products.price) > 400.00;
Run Example »FROM order_details
LEFT JOIN products on products.product_id = order_details.product_id
GROUP BY order_id
HAVING SUM(order_details.quantity * products.price) > 400.00;
Lists customers that have ordered for 1000$ or more:
Example
SELECT customers.customer_name,
SUM(order_details.quantity * products.price)
FROM order_details
INNER JOIN products ON order_details.product_id = products.product_id
INNER JOIN orders ON order_details.order_id = orders.order_id
INNER JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customers.customer_id, customers.customer_name
HAVING SUM(order_details.quantity * products.price) >= 1000;
Run Example »SUM(order_details.quantity * products.price)
FROM order_details
INNER JOIN products ON order_details.product_id = products.product_id
INNER JOIN orders ON order_details.order_id = orders.order_id
INNER JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customers.customer_id, customers.customer_name
HAVING SUM(order_details.quantity * products.price) >= 1000;
