Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: WHERE

The HAVING clause is used in conjunction with a GROUP BY so that aggregate functions can be used in a comparison. Think of it as a WHERE clause for groupings.

The following example should help to explain further.

The following example will fail because the WHERE clause is used to evaluate data on a row by row basis. The query is an attempt to limit the results returned to those customers with an order count greater than five.

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
WHERE COUNT(customer_id) > 5
GROUP BY customer_id

You will receive an aggregate related error, as the WHERE clause can only operate on a single row. To achieve the desired result, we remove the WHERE clause and add a HAVING clause after the GROUP By. Running the following query will now work:-

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
GROUP BY customer_id
HAVING COUNT(customer_id) > 5
Advertisements

Tags: , , , , ,

%d bloggers like this: