Microsoft SQL Server GROUP BY HAVING
Example
Because the WHERE clause is evaluated before GROUP BY, you cannot use WHERE to pare down results of the grouping (typically an aggregate function, such as COUNT(*)). To meet this need, the HAVING clause can be used.
For example, using the following data:
DECLARE @orders TABLE(OrderID INT, Name NVARCHAR(100))
INSERT INTO @orders VALUES
( 1, 'Matt' ),
( 2, 'John' ),
( 3, 'Matt' ),
( 4, 'Luke' ),
( 5, 'John' ),
( 6, 'Luke' ),
( 7, 'John' ),
( 8, 'John' ),
( 9, 'Luke' ),
( 10, 'John' ),
( 11, 'Luke' )
If we want to get the number of orders each person has placed, we would use
SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name
and get
| Name | Orders |
|---|---|
| Matt | 2 |
| John | 5 |
| Luke | 4 |
However, if we want to limit this to individuals who have placed more than two orders, we can add a HAVING clause.
SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name
HAVING COUNT(*) > 2
will yield
| Name | Orders |
|---|---|
| John | 5 |
| Luke | 4 |
Note that, much like GROUP BY, the columns put in HAVING must exactly match their counterparts in the SELECT statement. If in the above example we had instead said
SELECT Name, COUNT(DISTINCT OrderID)
our HAVING clause would have to say
HAVING COUNT(DISTINCT OrderID) > 2
