Having

The HAVING clause in SQL is used to filter the result set returned by a GROUP BY clause.

 

It allows you to apply conditions to groups of rows rather than individual rows.

 

This is particularly useful when you want to filter groups based on aggregate functions, such as SUM, AVG, MIN, MAX, etc.

 

Here are some examples:

 

 

Using SUM with HAVING:

SELECT department, SUM(sales) AS total_sales
FROM sales_table
GROUP BY department
HAVING SUM(sales) > 10000;

 

This query calculates the total sales for each department and filters out departments where the total sales exceed 10000.

 

 

Using AVG with HAVING:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

 

This query calculates the average salary for each department and filters out departments where the average salary is greater than 50000.

 

 

Using MAX with HAVING:

SELECT department, MAX(age) AS max_age
FROM employees
GROUP BY department
HAVING MAX(age) < 40;

 

 

 

WHERE and HAVING clauses in SQL are both used to filter rows in a query, but they operate at different stages of query execution and serve different purposes:

 

1. WHERE clause:

- The WHERE clause is used to filter rows before any grouping is performed.
- It is applied to individual rows in the original table(s) specified in the FROM clause.
- Conditions specified in the WHERE clause are evaluated against each row, and only rows that satisfy the conditions are included in the result set.
- The WHERE clause is typically used to filter rows based on individual column values or non-aggregated expressions.

 

2. HAVING clause:

- The HAVING clause is used to filter groups of rows after the grouping operation has been performed.
- It is applied to the result set after the GROUP BY clause has grouped the rows into sets based on common values.
- Conditions specified in the HAVING clause are evaluated against the aggregated values of each group, not individual rows.
- The HAVING clause is typically used to filter groups based on the results of aggregate functions, such as COUNT, SUM, AVG, etc.

 

In summary, the key difference between WHERE and HAVING is that WHERE filters rows before grouping, while HAVING filters groups after grouping. Use WHERE to filter individual rows based on column values, and use HAVING to filter groups based on aggregate values.