# Having (SQL)

> Mediated Wiki article. Canonical URL: https://mediated.wiki/source/Having_(SQL)
> Markdown URL: https://mediated.wiki/source/Having_(SQL).md
> Source: https://en.wikipedia.org/wiki/Having_(SQL)
> Source revision: 1328745217
> License: Creative Commons Attribution-ShareAlike 4.0 International (https://creativecommons.org/licenses/by-sa/4.0/)

SQL clause

This article needs additional citations for verification. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed. Find sources: "Having" SQL – news · newspapers · books · scholar · JSTOR (February 2024) (Learn how and when to remove this message)

A **HAVING** clause in [SQL](/source/SQL) specifies that an SQL [SELECT](/source/Select_(SQL)) statement must only return rows where aggregate values meet the specified [conditions](/source/Condition_(SQL)).[1]: 125–127

## Use

HAVING and WHERE are often confused by beginners, but they serve different purposes. WHERE is taken into account at an earlier stage of a query execution, filtering the rows read from the tables. If a query contains GROUP BY, rows from the tables are grouped and aggregated. After the aggregating operation, HAVING is applied, filtering out the rows that don't match the specified conditions. Therefore, WHERE applies to data read from tables, and HAVING should only apply to aggregated data, which isn't known in the initial stage of a query.

To view the present condition formed by the **GROUP BY** clause, the **HAVING** clause is used.[*[clarification needed](https://en.wikipedia.org/wiki/Wikipedia:Please_clarify)*]

## Examples

To return a list of department IDs whose total sales exceeded $1000 on the date of January 1, 2000, along with the sum of their sales on that date:

SELECT DeptID, SUM(SaleAmount)
FROM Sales
WHERE SaleDate = '2000-01-01'
GROUP BY DeptID
HAVING SUM(SaleAmount) > 1000

Referring to the [sample tables in the *Join* example](/source/Join_(SQL)#Sample_tables), the following query will return the list of departments which have more than 1 employee:

SELECT DepartmentName, COUNT(*)
FROM Employee
JOIN Department ON Employee.DepartmentID = Department.DepartmentID
GROUP BY DepartmentName
HAVING COUNT(*) > 1;

HAVING is convenient, but not necessary. Code equivalent to the example above, but without using HAVING, might look like:

SELECT * FROM (
    SELECT DepartmentName AS deptNam, COUNT(*) AS empCount
    FROM Employee AS emp
    JOIN Department AS dept ON emp.DepartmentID = dept.DepartmentID
    GROUP BY deptNam
) AS grp
WHERE grp.empCount > 1;

## References

1. **[^](#cite_ref-pgs23_1-0)** [*PostgreSQL 16.1 Documentation*](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-A4.pdf) (PDF). The PostgreSQL Global Development Group. 2023. Retrieved February 8, 2024.

## External links

- [The HAVING and GROUP BY SQL clauses](http://www.databasejournal.com/features/mysql/article.php/3469351) [Archived](https://web.archive.org/web/20110603062905/http://www.databasejournal.com/features/mysql/article.php/3469351) 2011-06-03 at the [Wayback Machine](/source/Wayback_Machine)

- [SQL Aggregate Functions](https://www.w3schools.com/sql/sql_functions.asp) [Archived](https://web.archive.org/web/20170503060710/https://www.w3schools.com/sql/sql_functions.asp) May 3, 2017, at the [Wayback Machine](/source/Wayback_Machine)

v t e SQL Versions SEQUEL SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2006 SQL:2008 SQL:2011 SQL:2016 SQL:2023 Keywords As Case Create Delete From Group by Having Insert Join Merge Null Order by Over Prepare Select Truncate Union Update With Related Edgar Codd Relational database ISO/IEC SQL parts Framework Foundation Call-Level Interface Persistent Stored Modules Management of External Data Object Language Bindings Information and Definition Schemas SQL Routines and Types for the Java Programming Language XML-Related Specifications

---
Adapted from the Wikipedia article [Having (SQL)](https://en.wikipedia.org/wiki/Having_(SQL)) by Wikipedia contributors ([contributor history](https://en.wikipedia.org/wiki/Having_(SQL)?action=history)). Available under [Creative Commons Attribution-ShareAlike 4.0 International](https://creativecommons.org/licenses/by-sa/4.0/). Changes may have been made.