SQL HAVING

SQL HAVING 설명

WHERE 키워드를 집계 함수와 함께 사용할 수 없기 때문에 HAVING 표현식이 SQL에 추가되었습니다.

SQL HAVING은 GROUP BY 문으로 생성된 그룹별로 값을 필터링하는 데 사용됩니다.

HAVING 구문

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s)

데모 데이터베이스

다음은 "Northwind" 데이터베이스의 "Customers"("고객") 테이블의 샘플입니다.

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	5021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	5023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

SQL HAVING 예

다음 SQL 쿼리는 각 국가의 고객 수를 나열합니다. 고객이 5명 이상인 국가만 포함됩니다.

Run SQLSELECT COUNT(CustomerID), Country 
FROM Customers 
GROUP BY Country 
HAVING COUNT(CustomerID) > 5

다음 SQL 쿼리는 각 국가의 고객 수를 가장 큰 것부터 가장 작은 것 순으로 정렬하여 나열합니다(고객이 5명 이상인 국가만 포함).

Run SQLSELECT COUNT(CustomerID), Country 
FROM Customers 
GROUP BY Country 
HAVING COUNT(CustomerID) > 5 
ORDER BY COUNT(CustomerID) DESC

데모 데이터베이스

"Orders"("주문") 테이블의 샘플을 살펴보겠습니다.

ProductID	OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
1	10248	90	5	1996-07-04	3
2	10249	81	6	1996-07-05	1
3	10250	34	4	1996-07-08	2
4	10251	84	3	1996-07-08	1
5	10252	76	4	1996-07-09	2

그리고 "Employees"("Employees") 테이블의 샘플은 다음과 같습니다.

EmployeeID	LastName	FirstName	BirthDate	Photo	Notes
1	Davolio	Nancy	12/8/1968	EmpID1.pic	Education includes a BA in psychology from Colorado State University. She also completed (The Art of the Cold Call). Nancy is a member of 'Toastmasters International'.
2	Fuller	Andrew	2/19/1952	EmpID2.pic	Andrew received his BTS commercial and a Ph.D. in international marketing from the University of Dallas. He is fluent in French and Italian and reads German. He joined the company as a sales representative, was promoted to sales manager and was then named vice president of sales. Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.
3	Leverling	Janet	8/30/1963	EmpID3.pic	Janet has a BS degree in chemistry from Boston College). She has also completed a certificate program in food retailing management. Janet was hired as a sales associate and was promoted to sales representative.
4	Peacock	Margaret	9/19/1958	EmpID4.pic	Margaret holds a BA in English literature from Concordia College and an MA from the American Institute of Culinary Arts. She was temporarily assigned to the London office before returning to her permanent post in Seattle.
5	Buchanan	Steven	3/4/1955	EmpID5.pic	Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree. Upon joining the company as a sales representative, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London, where he was promoted to sales manager. Mr. Buchanan has completed the courses 'Successful Telemarketing' and 'International Sales Management'. He is fluent in French.

더 많은 HAVING 예

다음 SQL 쿼리는 10개 이상의 주문을 등록한 직원을 나열합니다.

Run SQLSELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders 
FROM (Orders 
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) 
GROUP BY LastName 
HAVING COUNT(Orders.OrderID) > 10

다음 SQL 문은 "Davolio" 또는 "Fuller" 직원이 25개 이상의 주문을 등록한 경우 데이터를 표시합니다.

Run SQLSELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders 
FROM Orders 
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID 
WHERE LastName = 'Davolio' OR LastName = 'Fuller' 
GROUP BY LastName 
HAVING COUNT(Orders.OrderID) > 25