CODEWITHSUNDEEP
sql-serverhaving-clause
Mark Shevchenko
Mark Shevchenko

Reputation: 8197

Can I use HAVING instead of WHERE in SQL queries?

I always thought that I could not, but MSDN says otherwise.

When GROUP BY is not used, HAVING behaves like a WHERE clause.

I had checked and got the error:

Msg 8121:
Column '...' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY.

So, what is it? An error in documentation, or a little-known detail?

This may be one of those things that worked in Sybase SQL Server. Now Microsoft rewrote SQL Server closer to the ANSI standard, but forgot to fix documentation.

This is the case?

Upvotes: 4

Views: 1933

Answers (1)

Adil
Adil

Reputation: 148110

You can use having without using group by but on aggregate function.

select avg(price) from tbltemp having avg(price) >= 2

In the MSDN link you provided, the example given instructs us to use where instead of having.

You may NOT write something like "select avg(price) from goods HAVING price >= 1000" but you may write "select avg(price) from goods WHERE price >= > 1000"

Upvotes: 6

Related Questions