HAVING

You can use the "Having" clause to specify a search condition for a group of rows
You use the HAVING clause to specify which groups are to be displayed
If you include a GROUP BY clause before the HAVING clause, the applies to each of the groups formed by equal values in the specified columns. If you don't include a GROUP BY clause then the applies to the entire logical table defined by the SELECT statement


The HAVING clause can precede the GROUP BY but it recommended that you have the GROUP BY clause first as its more logical. Groups are formed and group functions are calculated before the HAVINg clause is applied to the groups in the SELECT list


Uses the same operators and syntax as the WHERE clause.


SELECT CustomerID 
       ,COUNT(ProductID)
FROM Transactions
GROUP BY CustomerID
HAVING COUNT(ProductID) > 2



The WHERE clause can be used with the HAVING clause because it allows you to filter the rows before the data is grouped.
Useful when you want to filter groups and items that are not on the same query


SELECT CustomerID 
        COUNT(ProductID)
FROM Transactions
WHERE CustomerID <= 6
GROUP BY CustomerID
HAVING COUNT(ProductID) > 2



This works in both SQL Server and Access

SELECT     Country 
      ,COUNT(Country)
FROM INVOICES
GROUP BY Country
HAVING Country = 'Denmark'
ORDER BY Country ASC



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext