CODEWITHSUNDEEP
sqlsql-server
Thunder
Thunder

Reputation: 10986

Replace where with having

I have a group by - having query , Can I add additional where conditions to having clause instead of adding extra where clause.Will the result be same?Will there be any case when all of where could not be added to a having clause. I require this since I am working on a query generation tool , and this specific feature will save me some work on UI and query generation logic.

eg 

USE AdventureWorks ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00 and  SalesOrderID>43875 and SalesOrderID <46066
ORDER BY SalesOrderID

and 

USE AdventureWorks ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
where  SalesOrderID>43875 and SalesOrderID <46066
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00 
ORDER BY SalesOrderID

Upvotes: 0

Views: 835

Answers (2)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

WHERE is used to filter/condition the result set before you run GROUP BY.

HAVING is used to filter/condition the result set after you have grouped.

So while in some situations you can get the same result - the two operators aren't the same and don't do the same, so you should use the proper operator for what you're trying to achieve.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The results will be the same if the columns referenced are in the group by clause.

However, putting them in the where clause is advantageous, because SQL Server can eliminate rows before doing the aggregation. This is usually more efficient than removing them afterwards. It is possible that SQL Server has an optimization for this that I'm not familiar with.

You cannot add conditions to the having clause if they involve columns that are not in the group by.

Upvotes: 4

Related Questions