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”의 35개의 댓글

  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. Area on with this write-up, I really think this website needs much more factor to consider. I?ll probably be once more to check out a lot more, thanks for that details.

  11. 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.

  12. 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.

  13. 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!

댓글 남기기