Aggregates

SQL HAVING

Filtering Groups with HAVING

HAVING filters grouped data post-GROUP BY, refining aggregate results.

Introduction to SQL HAVING Clause

The SQL HAVING clause is used to filter records that work with aggregate functions. Unlike the WHERE clause, which is used to filter rows before grouping, the HAVING clause filters data after the GROUP BY operation. This allows for refined control over the results of aggregate queries, making it a powerful tool for data analysis.

Difference Between WHERE and HAVING

The WHERE clause and the HAVING clause serve different purposes in SQL. While WHERE is used to filter rows before any grouping, HAVING is used to filter groups after they have been formed by GROUP BY. It is crucial to understand this difference to properly utilize both clauses in queries.

Using HAVING with Aggregate Functions

The HAVING clause is often used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX. This allows you to filter the results of these functions to include only the groups that meet specific criteria.

Example: Filtering Aggregate Results

Consider a company database where you want to find departments with an average salary greater than $60,000. Using the HAVING clause, you can filter the departments after calculating the average salary for each.

Combining WHERE, GROUP BY, and HAVING

It's common to use WHERE and HAVING together in a query. Use WHERE to filter individual rows and HAVING to filter aggregated data. This dual filtering mechanism provides powerful querying capabilities.

SQL Aggregates

Previous: GROUP BY
Next: Scalar Subqueries