WHERE and HAVING clause in Microsoft SQL-Server

 WHERE and HAVING clause in Microsoft SQL-Server

People may get confused many time about usage of WHERE and HAVING clause. I am just trying to throw little bit light on WHERE and HAVING clause to make its concept clear. In my many interview session, I asked this simple question to lot of candidate and many of them are aware with “WHERE” condition but not or partial aware with “HAVING” so I felt to write this simple article for them.

Note: This article is moved to following link on 6th NOVE 2012. Please click on following link to read full article with TSQL Script.

http://blog.extreme-advice.com/2009/03/05/where-and-having-clause-in-microsoft-sql-server/

Advertisements

32 Responses to “WHERE and HAVING clause in Microsoft SQL-Server”

  1. ting Says:

    very good explanation that I am looking for.

  2. Satish Says:

    Hii

    can i use 2 having clause in one sql query ???

    • riteshshah Says:

      why you need two HAVING clause??? It is not possible but if you elaborate your requirement, I can suggest you some different approach.

  3. Satish Says:

    Hii

    can i use 2 having clause in one sql query ???
    pls help me

  4. NarayanSwamy Says:

    Superb Explanation. It s really useful

  5. Dhilip Says:

    Awesome example… Basic learners can easily understand

  6. Selvaraj Says:

    Really good one. Thank you.

  7. Lucky Says:

    Good 1… easy to undstd.

  8. Bala Sakthis Says:

    Very good explanation. Registers quickly in the mind. Thanks! for your post.

  9. Nosnetrom Says:

    JUST what I needed. Thanks!

  10. Bell Says:

    I have 2 columns A, B, and C in a Table X. Column A has the ‘Apple’ repeating for 100 odd rows. Column C has the time. And column B has has 4 different numbers. These numbers appear in a sequence at certain time intervals and appear randomly in others. How do I query so that it returns only the rows with numbers appearing sequentially (i.e., filter out the random numbers)?

    Thanks much.

  11. Jyoti Says:

    hi ritesh,
    thanks for nice explaination.
    i am bit confused in following lines
    Moreover, “HAVING” clause can be used in SELECT statement with “GROUP BY” clause and whenever “GROUP BY’ is not present, “HAVING” will work as “WHERE”.

    you mean we can use “HAVING” clause without presence of “GROUP BY” clause????

    • riteshshah Says:

      Hello Jyoti,

      Yes, we can use HAVING clause without presence of “Group By” clause. for eg:

      SELECT SUM(NumericColumn1) from TableName HAVING SUM(SUM(NumericColumn2)=10

      • Sanjeet Says:

        We can not use having clause without using Group by clause . It gives error.

      • riteshshah Says:

        have you seen the example I have provided in the blog? isn’t it working? can you please show me your query along with the SQL Server version information you are using?

  12. riteshshah Says:

    BTW, try running this query in same table given in blog post

    SELECT AVG(total) AS ‘Average’ FROM blogcount

    WHERE Topic=’SQL’

    HAVING AVG(total)>25

  13. tg Says:

    tell me use having function
    and how to use this function

    • riteshshah Says:

      User of HAVING with/without group by is already given in article and in few comments. if you have any specific question/concern/confusion, kindly let me know.

  14. Senthil Kumar D Says:

    Senthil

    @Ritesh

    Really Cool example. It will definitely help for the novice in Sql…

    Thank U

  15. SARATH CHANDRA PRASAD Says:

    hi
    is possible for where and having is in same query or not

    • riteshshah Says:

      Hi Sarath,

      Yes, it is possible for sure. Look at the comments I have given in previously in this article. It has an example of the same.

  16. Sid Says:

    Worth reading this example to get idea about WHERE n HAVING clause. Thnx Ritesh…

  17. Ajay Says:

    Thanks ,It is very nice examples.

  18. Ajay Yadav Says:

    Simple and sober……

  19. vidya Says:

    Thank you Ritesh..
    really its very simple and helpful.

  20. mahesh Says:

    thanks

  21. anita yadav Says:

    anita says:
    thanx realy its very helpful…………..

  22. custom database design Says:

    custom database design…

    […]WHERE and HAVING clause in Microsoft SQL-Server « Ritesh’s Blog for SQL-SERVER & .NET[…]…

  23. Sree Says:

    select City_18432, avg(Salary_18429) from form.EmpTable_1128
    where form.EmpTable_1128.Salary_18429 15000 is not working in sql server 2005

    • riteshshah Says:

      What error are you getting? I dont see any comparison operator in the query you have given here

  24. Amarendra singh Says:

    this blog very imporat for me , i really rally thank full to u.
    our concept clear for this example..

  25. Kashyap Vyas Says:

    gud..thanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: