Use the HAVING clause to filter grouped data after GROUP BY, especially when the conditions involve aggregation functions such as COUNT(), SUM(), AVG(); 2. Unlike the WHERE clause, WHERE is used to filter a single row before grouping, while HAVING is used to filter groups based on the aggregation result after grouping; 3. HAVING must be placed after GROUP BY, and column alias in SELECT cannot be used, and the aggregate expression must be repeated; 4. WHERE and HAVING can be used at the same time, the former filters the original row and the latter filters the grouping results; 5. Common application scenarios include finding customers whose order count exceeds the specified value, departments whose average salary is higher than a certain value, or excluding groups with NULL values; 6. Summary: When you need to filter groups based on the aggregation results, HAVING must be used, which is the key mechanism for implementing group-level conditional filtering in SQL.

The HAVING clause in SQL is used to filter groups of rows after the GROUP BY clause has been applied. Unlike the WHERE clause, which filters individual rows before grouping, HAVING filters after grouping, allowing you to apply conditions on aggregate functions like COUNT() , SUM() , AVG() , etc.

When to Use HAVING instead of WHERE

Use HAVING when your filter condition involves aggregated data. For example:

• "Show departments with more than 5 employees" → needs COUNT() , so use HAVING
• "Show employees named 'John'" → filters individual rows, use WHERE

Basic Syntax

 SELECT column, AGGREGATE_FUNCTION(expression)
FROM table_name
WHERE condition (optional)
GROUP BY column
HAVING aggregate_condition;

Note: HAVING comes after GROUP BY .

Example: Filtering Groups with HAVING

Suppose you have a table sales :

You want to find regions where total sales exceed 200.

 SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 200;

Results:

Even though the South region has two entries, their total is 300? Wait — let's reccalculate:

• North: 100 150 50 = 300
• South: 200 100 = 300

So both are above 200 → both should appear.

 -- Correct result will include both North and South

But if you change the threshold:

 HAVING SUM(amount) > 300; -- Then neither qualifies

Or maybe:

 HAVING COUNT(salesperson) >= 2; -- Regions with at least 2 sales records

This would return both regions again in this case.

Common Use Cases

• Find customers with more than 3 orders:

   SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(*) > 3;

• Find departments where average salary is above $70,000:

   SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
  HAVING AVG(salary) > 70000;

• Exclude groups with missing data:

   SELECT project_id, AVG(hours_worked)
  FROM time_logs
  GROUP BY project_id
  HAVING COUNT(hours_worked) = COUNT(*); -- Ensures no NULLs in hours_worked

Key Points

• Always use HAVING with GROUP BY (except in some databases where it's allowed without, but it's rare).

• You cannot use aliases from the SELECT clause in the HAVING clause (because HAVING is logically processed before SELECT ).

   -- This might fail or be unsupported:
  SELECT region, SUM(amount) AS total
  FROM sales
  GROUP BY region
  HAVING total > 200; -- ? Using alias 'total' here is invalid in most SQL dialects

  Instead, repeat the expression:

   HAVING SUM(amount) > 200; -- ? Correct

• You can combine WHERE and HAVING :

   SELECT region, COUNT(*) 
  FROM sales
  WHERE amount > 50 -- Filters rows before grouping
  GROUP BY region
  HAVING COUNT(*) > 1; -- Filters groups after aggregation

  ---

  So, in short: use HAVING when you need to filter based on group-level summaries. It's essential for meaningful reporting and analysis in SQL.

  Basically, if your WHERE can't handle aggregate conditions, HAVING steps in.

  The above is the detailed content of How do you use the HAVING clause to filter groups in SQL?. For more information, please follow other related articles on the PHP Chinese website!