Previous
Written by: rJTutorial Team | Last Updated:

SQL HAVING

The HAVING clause is a powerful tool in SQL designed specifically for filtering data after an aggregation has taken place. If you have ever tried to use a WHERE clause to filter by a sum or an average and received an error, the HAVING clause is exactly what you were missing. It acts as a secondary filter that operates on grouped sets of data rather than individual rows.

Developer Tip: Think of WHERE as a pre-filter (happening before the data is summarized) and HAVING as a post-filter (happening after the summary is calculated).

 

Syntax of SQL HAVING

SELECT column_name, aggregate_function(column_name)  
FROM table_name  
GROUP BY column_name  
HAVING aggregate_condition;
  • column_name: The specific attribute you want to group your results by (e.g., Category, Region, or CustomerID).
  • aggregate_function(): Mathematical operations performed on your data, such as SUM(), COUNT(), AVG(), MAX(), or MIN().
  • table_name: The source table containing your data.
  • condition: The rule applied to the result of your aggregate function (e.g., "where the total sales are greater than $1,000").
Best Practice: Even though some databases allow it, always include the column you are grouping by in your SELECT statement. This makes your results much easier to read and debug.

 

Key Points

  • The HAVING clause is essential because the WHERE keyword cannot be used with aggregate functions like SUM() or COUNT().
  • It is primarily used in conjunction with the GROUP BY clause to analyze trends across categories.
  • In the SQL order of execution, HAVING is processed after the FROM, WHERE, and GROUP BY clauses, but before SELECT and ORDER BY.
Watch Out: Using HAVING without a GROUP BY clause will treat the entire table as a single group. While valid in some SQL dialects, it is rarely what you actually want to do.

 

Example of SQL HAVING

To understand HAVING, let's look at a typical e-commerce scenario. Suppose we have a table that tracks every individual item ordered by customers.

Table: Orders

order_id customer_name product_name quantity
1 Alice Apple 3
2 Bob Apple 2
3 Alice Banana 5
4 Charlie Banana 7
5 Bob Cherry 4

To find customers who ordered more than 5 items in total across all their orders:

SELECT customer_name, SUM(quantity) AS total_quantity  
FROM orders  
GROUP BY customer_name  
HAVING SUM(quantity) > 5;

Output:

customer_nametotal_quantity
Alice8
Charlie7

Explanation:

  • GROUP BY customer_name: This tells the database to bundle all rows belonging to "Alice" together, all rows for "Bob" together, and so on.
  • SUM(quantity): This calculates the total count of items for each person's "bundle."
  • HAVING SUM(quantity) > 5: This looks at the final sums (Alice=8, Bob=6, Charlie=7) and removes Bob from the list because his total is not strictly greater than 5.
Common Mistake: Trying to use the alias (like total_quantity) in the HAVING clause. Some SQL engines (like MySQL) allow this, but standard SQL requires you to repeat the aggregate function (SUM(quantity)) in the HAVING clause.

 

SQL HAVING vs. WHERE

The difference between these two boils down to when the filtering happens. Use WHERE to exclude raw data before any math happens. Use HAVING to filter the results of that math.

FeatureWHERE ClauseHAVING Clause
Applied onIndividual rows before groupingSummarized groups after grouping
Works with Aggregate Functions?No (e.g., WHERE SUM(x) fails)Yes (e.g., HAVING SUM(x) works)
Execution OrderFirst (Filtering source data)Last (Filtering grouped results)

Example: Find orders where individual quantity is greater than 3 (using WHERE)

This filters the raw rows before any grouping occurs. Only the individual entries for Alice and Charlie meet this criteria.

SELECT *  
FROM orders  
WHERE quantity > 3;

Example: Find customers who ordered more than 3 items in total (using HAVING)

This adds up all orders for each customer first, and then checks the total.

SELECT customer_name, SUM(quantity) AS total_quantity  
FROM orders  
GROUP BY customer_name  
HAVING SUM(quantity) > 3;
Best Practice: If you can filter data using WHERE, do it! It is much more efficient to remove rows before the database spends resources grouping them. Only use HAVING for conditions that specifically require aggregated values.

 

SQL HAVING with Multiple Conditions

Just like the WHERE clause, HAVING supports complex logic using AND and OR. This is incredibly useful for deep reporting and data analysis.

Example: Find products with a total quantity greater than 5 and an average order size greater than 2:

SELECT product_name, SUM(quantity) AS total_quantity, AVG(quantity) AS avg_quantity  
FROM orders  
GROUP BY product_name  
HAVING SUM(quantity) > 5 AND AVG(quantity) > 2;

 

SQL HAVING with COUNT()

The COUNT() function combined with HAVING is a classic way to identify duplicates or frequent users. For instance, you might want to identify "Power Users" who have visited your site or made purchases multiple times.

Example: Find customers who placed more than 1 order:

SELECT customer_name, COUNT(order_id) AS order_count  
FROM orders  
GROUP BY customer_name  
HAVING COUNT(order_id) > 1;

Output:

customer_nameorder_count
Alice2
Bob2

 

Summary

  • HAVING provides a way to filter the results of aggregate functions like SUM, COUNT, and AVG.
  • It acts as a filter for groups created by the GROUP BY clause.
  • Order Matters: Always place HAVING after GROUP BY and before ORDER BY.
  • Optimization: Use WHERE for row-level filters and HAVING for group-level filters to keep your queries running fast.
Previous