SQL HAVING Clause

The SQL HAVING syntax

The general syntax is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE condition
  4. GROUP BY columnnames
  5. HAVING condition

The general syntax with ORDER BY is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE condition
  4. GROUP BY columnnames
  5. HAVING condition
  6. ORDER BY columnnames

 


 

SQL GROUP BY Examples

Problem: List the number of customers in each country. Only include countries with more than 10 customers.

  1. SELECT COUNT(Id), Country
  2. FROM Customer
  3. GROUP BY Country
  4. HAVING COUNT(Id) > 10

Results: 3 records

Count Country
11 France
11 Germany
13 USA

 


Problem: List the number of customers in each country, except the USA, sorted high to low.
Only include countries with 9 or more customers.

  1. SELECT COUNT(Id), Country
  2. FROM Customer
  3. WHERE Country <> ‘USA’
  4. GROUP BY Country
  5. HAVING COUNT(Id) >= 9
  6. ORDER BY COUNT(Id) DESC

Results: 3 records

Count Country
11 France
11 Germany
9 Brazil

 


Problem: List all customer with average orders between $1000 and $1200.

  1. SELECT AVG(TotalAmount), FirstName, LastName
  2. FROM [Order] O JOIN Customer C ON O.CustomerId = C.Id
  3. GROUP BY FirstName, LastName
  4. HAVING AVG(TotalAmount) BETWEEN 1000 AND 1200

Results: 10 records

Average FirstName LastName
1081.215000 Miguel Angel Paolino
1063.420000 Isabel de Castro
1008.440000 Alexander Feuer
1062.038461 Thomas Hardy
1107.806666 Pirkko Koskitalo
1174.945454 Janete Limeira
1073.621428 Antonio Moreno
1065.385000 Rita Müller
1183.010000 José Pedro Freyre
1057.386666 Carine Schmitt

“SQL HAVING Clause”의 122개의 댓글

  1. We stumbled over here coming from a different website and thought I may as well check things out.
    I like what I see so now i am following you. Look forward to going over your web page yet again.

  2. You are so awesome! I do not think I’ve truly read through anything like this before.
    So nice to find someone with a few original thoughts on this issue.
    Seriously.. thanks for starting this up. This site is something that is needed on the web, someone with
    some originality!

  3. Pretty component of content. I just stumbled upon your web site and in accession capital to say that
    I acquire actually loved account your weblog posts.
    Any way I’ll be subscribing on your augment and even I success you access persistently rapidly.

  4. Admiring the commitment you put into your blog and detailed information you
    present. It’s great to come across a blog every once in a while that isn’t the same out of date rehashed information. Great read!

    I’ve saved your site and I’m including your RSS feeds to my Google account.

  5. I simply want to tell you that I am very new to blogs and actually savored you’re blog. More than likely I’m going to bookmark your site . You certainly have fantastic article content. With thanks for revealing your website.

  6. Hey there! I know this is kinda off topic however , I’d figured I’d ask. Would you be interested in trading links or maybe guest authoring a blog post or vice-versa? My site discusses a lot of the same subjects as yours and I think we could greatly benefit from each other. If you happen to be interested feel free to shoot me an email. I look forward to hearing from you! Wonderful blog by the way!

  7. A remarkable share, I simply offered this onto an associate who was doing a little analysis on this. And he as a matter of fact got me morning meal because I found it for him. smile. So let me rephrase that: Thnx for the reward! Yet yeah Thnkx for spending the moment to review this, I feel strongly concerning it and also enjoy finding out more on this topic. Preferably, as you come to be knowledge, would you mind upgrading your blog site with even more details? It is extremely helpful for me. Big thumb up for this blog post!

  8. Great message. I learn something much more challenging on various blog sites daily. It will always be promoting to check out material from other authors and practice a something from their shop. I?d like to utilize some with the web content on my blog whether you don?t mind. Natually I?ll provide you a web link on your internet blog. Thanks for sharing.

  9. Great blog post. I find out something a lot more difficult on different blog sites daily. It will constantly be stimulating to check out material from other authors and also exercise a something from their store. I?d prefer to utilize some with the web content on my blog whether you don?t mind. Natually I?ll offer you a web link on your web blog. Thanks for sharing.

  10. I am commonly to blogging and i really value your web content. The short article has actually peaks my passion. I am going to bookmark your site as well as maintain looking for brand-new info.

  11. I am usually to blog writing and i really appreciate your web content. The post has actually peaks my rate of interest. I am going to bookmark your website and keep checking for new info.

  12. Hello! This is my 1st comment here so I just wanted to give a quick shout out and tell you I genuinely enjoy reading through your posts. Can you suggest any other blogs/websites/forums that deal with the same topics? Thank you so much!

  13. There are definitely a great deal of details like that to consider. That is a great point to bring up. I use the thoughts above as basic ideas however plainly there are inquiries like the one you bring up where one of the most essential thing will certainly be working in honest good faith. I don?t recognize if finest methods have actually emerged around things like that, but I make sure that your job is clearly determined as an up for grabs. Both kids and women really feel the influence of just a moment?s enjoyment, for the remainder of their lives.

  14. Youre so trendy! I do not suppose Ive review anything such as this prior to. So good to find somebody with some initial ideas on this subject. realy thank you for starting this up. this site is something that is required on the web, someone with a little creativity. helpful job for bringing something new to the internet!

  15. There are some intriguing moments in this post but I don?t understand if I see all of them center to heart. There is some validity however I will certainly take hold viewpoint up until I check out it even more. Excellent short article, many thanks and also we want much more! Added to FeedBurner too

  16. 핑백: Kobe Bryant Website Official

  17. I?m pleased, I have to claim. Actually hardly ever do I run into a blog site that?s both enlightening and also entertaining, as well as let me inform you, you have hit the nail on the head. Your suggestion is impressive; the concern is something that inadequate individuals are speaking wisely about. I am really pleased that I stumbled across this in my search for something connecting to this.

  18. There are some intriguing points in time in this post however I don?t understand if I see all of them facility to heart. There is some credibility however I will take hold viewpoint until I consider it further. Great write-up, many thanks as well as we desire much more! Contributed to FeedBurner also

  19. I?m pleased, I need to say. Truly rarely do I experience a blog site that?s both informative as well as amusing, and also let me tell you, you have actually hit the nail on the head. Your suggestion is impressive; the concern is something that inadequate people are talking intelligently around. I am extremely satisfied that I came across this in my look for something connecting to this.

  20. When I originally commented I clicked the -Alert me when brand-new remarks are added- checkbox and currently each time a remark is added I get 4 emails with the very same remark. Is there any way you can eliminate me from that service? Thanks!

  21. After study a few of the post on your site currently, and I genuinely like your method of blog writing. I bookmarked it to my book marking web site list and will certainly be examining back quickly. Pls take a look at my website too and also let me understand what you believe.

  22. There are some interesting times in this article but I don?t understand if I see every one of them facility to heart. There is some legitimacy yet I will hold opinion till I look into it further. Good short article, many thanks and also we desire more! Included in FeedBurner as well

  23. An impressive share, I just given this onto a coworker that was doing a little evaluation on this. As well as he in fact acquired me breakfast because I discovered it for him. smile. So let me rephrase that: Thnx for the reward! However yeah Thnkx for investing the time to review this, I really feel strongly about it and like reading more on this subject. Preferably, as you end up being know-how, would certainly you mind updating your blog with more details? It is extremely practical for me. Big thumb up for this post!

  24. A remarkable share, I simply provided this onto a colleague that was doing a little evaluation on this. And he as a matter of fact bought me breakfast since I located it for him. smile. So let me rephrase that: Thnx for the treat! However yeah Thnkx for spending the time to discuss this, I really feel strongly concerning it as well as enjoy finding out more on this topic. When possible, as you end up being competence, would you mind upgrading your blog site with even more details? It is highly valuable for me. Big thumb up for this article!

  25. 핑백: buffstreams

  26. I was really itching to fix it some wager some money on some sports matches that are circumstance fitting now. I wanted to allow in you guys be familiar with that I did understand what I ruminate on to be the kindest plat in the USA.
    If you poverty to get in on the action, verify it out of the closet: [url=https://onlinegambling4money.com/]online gambling slot machines[/url]

답글 남기기