SQL 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
- COUNT
- SUM
- AVG
- MIN and MAX
- GROUP BY
- HAVING
- Previous
- GROUP BY
- Next
- Scalar Subqueries
