SQL Having

The SQL HAVING clause is used in combination with the GROUP BY to filter the results by conditions that involve aggregate functions. While the WHERE clause filters individual rows, the HAVING filters groups of rows based on aggregate values.

Here’s an example of a basic statement:

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > 10;

Suppose you have an e-commerce order_lines table, and you want to see all orders totaling more than $10.

SELECT order_id, SUM(price)
FROM order_lines
GROUP BY order_id
HAVING SUM(price) > 10;

This statement would return a table with one line per order totaling more than 10, with per-order sum of item price.

Multiple conditions may be used, and in some databases, the HAVING clause may refer to a column alias.  The following similar query adds a count of number of products ordered in a column named products_ordered, then uses that column alias in the HAVING clause to also limit the results to orders of more than one product.

SELECT order_id, SUM(price), COUNT(DISTINCT product_id) as products_ordered
FROM order_lines
GROUP BY order_id
HAVING products_ordered > 1 AND SUM(price) > 10;

This result means that you have only one book that costs 14 and three costing 12.

Remember the SQL HAVING clause is combined with the GROUP BY. So, you must specify the grouping criteria before using HAVING to filter the results by aggregate values. Plus, it is a Boolean expression condition (true or false), and you can also use other clauses like ORDER BY and LIMIT to set the sorting and number of results the query returns.