laika222
10/31/2016 - 6:37 PM

HAVING

HAVING is like WHERE except that it acts on a group of rows. HAVING is usually preceded by a GROUP BY clause, and is used used with aggregate functions that act on groups of rows, such as COUNT(), AVG(), SUM(), MIN(), etc. In line 5, the results are grouped by CustomerID, and the HAVING clause in line 6 selects only groups that have 2 or more orders.

HAVING SS & MS.sql
content_copyfile_download
-- HAVING filters the result set of an aggregrate function rather than filtering the input set (the pre-aggregate set). WHERE happens before the aggregation, and HAVING happens after the aggregation.

SELECT Customers.CustomerID, Customers.LastName, Customers.FirstName, COUNT(Orders.OrderID)
FROM Customers
JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
GROUP BY Customers.CustomerID
HAVING COUNT(Orders.OrderID) >= 2
ORDER BY COUNT(Orders.OrderID);