Please navigate to the bottom of the page for Table of Contents

Sunday, June 12, 2011

SQL GROUP BY and HAVING clauses

In the previous post we explored the basics of SQL SELECT statement. In this post we will continue to look at some more complex questions and answers involving SELECT statement.

Using DISTINCT with SELECT

The following example uses DISTINCT to prevent the retrieval of duplicate titles.

SELECT DISTINCT JobTitle
FROM Employee
ORDER BY JobTitle;


GROUPing data using GROUP BY


The GROUP BY clause is used in a SELECT query to determine the groups that rows should be put in. GROUP BY follows the optional WHERE clause and is most often used when aggregate functions are referenced in the SELECT statement.


The following example finds the total of each sales order in the database. Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each sales order.


SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM .SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;


The following example finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID.


SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price', 
SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID;



The following example groups by an expression. You can group by an expression if the expression does not include aggregate functions.


SELECT AVG(OrderQty) AS 'Average Quantity', 
NonDiscountSales = (OrderQty * UnitPrice)
FROM SalesOrderDetail
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC;



The following example finds the average price of each type of product and orders the results by average price.


SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY AVG(UnitPrice);



By adding the ALL keyword after GROUP BY, all row values are used in the grouping, even if they were
not qualified to appear via the WHERE clause as shown below.


SELECT OrderDate, SUM(TotalDue) TotalDueByOrderDate
FROM SalesOrderHeader
WHERE OrderDate BETWEEN '5/1/2011' AND '5/31/2011'
GROUP BY ALL OrderDate


Filter Grouped Data with HAVING clause


The HAVING clause is a filter that acts similar to a WHERE clause, but the filter acts on groups of rows rather than on individual rows. In other words, the HAVING clause is used to qualify the results after the GROUP BY has been applied. The WHERE clause, in contrast, is used to qualify the rows that are returned before the data is aggregated or
grouped. HAVING qualifies the aggregated data after the data has been grouped or aggregated.


The example below shows a HAVING clause with an aggregate function. It groups the rows in the SalesOrderDetail table by product ID and eliminates products whose average order quantities are five or less.


SELECT ProductID 
FROM SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;


This example shows a HAVING clause without aggregate functions. This query uses the LIKE clause in the HAVING clause.


SELECT SalesOrderID, CarrierTrackingNumber 
FROM SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;

The following example shows using GROUP BY, HAVING, WHERE, and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the products with prices over $25 and average order quantities under 5. It also organizes the results by ID.
SELECT ProductID 
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;




Aggregate functions such as SUM and AVG can also be using in the HAVING clause. For example, to see the products that have had total sales greater than $2000000.00, the following query would be needed.


SELECT ProductID, Total = SUM(LineTotal)
FROM SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00;



If you want to make sure there are at least 1500 items involved in the calculations for each product, use HAVING COUNT(*) > 1500 to eliminate the products that return totals for fewer than 1500 items sold.


SELECT ProductID, SUM(LineTotal) AS Total
FROM SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500;


Creating tables with SELECT INTO


The following example creates a temporary table named #Bicycles in tempdb.


USE tempdb;
GO
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT *
INTO #Bicycles
FROM AdventureWorks2008R2.Production.Product
WHERE ProductNumber LIKE 'BK%';



This second example creates the permanent table NewProducts.


IF OBJECT_ID ('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts;
GO

SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
AND ListPrice < $100;

48 comments:

  1. what indicate the n and u parameter in object_id method

    ReplyDelete
    Replies
    1. Hello Nikhil,

      Great info! I recently came across your blog and have been reading along.
      I thought I would leave my first comment. I don’t know what to say except that I have

      We have a linked server "LinkSQL" defined on SQLServerA against the same server, ie, SQLServerA and the server has Named Pipes disabled. Isn't communication among'st applications within one server happens primarily through Named Pipes. If so, would it be better if we have Named Pipes enabled on this SQLServer box?
      Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
      We are seeing intermittent TCP/Semaphore errors and I was wondering if enabling this option would help.
      But nice Article Mate! Great Information! Keep up the good work!

      Thank you,
      Kevin

      Delete
  2. Hello,
    very impressive and appreciate for sharing with us. Know more about SQL Interview Questions

    ReplyDelete
  3. VERY INFORMATIVE BLOG. KEEP SHARING SUCH A GOOD ARTICLES.

    Best Software company in New jersey, USA

    ReplyDelete


  4. wow...nice blog, very help full information. Thanks for sharing.

    ReplyDelete
  5. Very good informative article. Thanks for sharing such nice article, keep on up dating such good articles.
    Best Cloud Solutions | Austere Technologies

    ReplyDelete
  6. Really great blog, it's very helpful and has great knowledgeable information. Thanks for sharing, keep updating such a good informative blog.

    Quality Managment Services | Austere Technologies

    ReplyDelete
  7. Great article, really very helpful content you made. Thank you, keep sharing.

    Internet Of Things(‭IOT) Services | Austere Technologies

    ReplyDelete
  8. Very good informative article. Thanks for sharing such nice article, keep on up dating such good articles
    Austere Technologies|Mobility

    ReplyDelete
  9. Excellent information you made in this blog, very helpful information. Thanks for sharing.

    Best chartered accountant courses| Avinash college of commerce

    ReplyDelete
  10. Great article, really very helpful content you made. Thank you, keep sharing.

    Best Software Testing Services | Austere Technology

    ReplyDelete
  11. Nice set of interview questions and answers.i have gone through it very helpful one thanks for sharing...
    SQL Interview Questions & Answers.

    ReplyDelete
  12. Really i appreciate the effort you made to share the knowledge. The topic here i found was really effective...

    Softgen Infotech have the best Python Training in Bangalore . Any professional who is looking out to switch their career can enroll with us.

    ReplyDelete
  13. Such a great word which you use in your article and article is amazing knowledge. thank you for sharing it.

    Looking for SAP FICO Training in Bangalore , learn from eTechno Soft Solutions SAP HR HCM Training on online training and classroom training. Join today!

    ReplyDelete
  14. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.MSBI Training in Bangalore


    ReplyDelete
  15. wow...nice blog, very help full information. Thanks for sharing...
    Informatica Training in Bangalore

    ReplyDelete
  16. We can say that ML is the subset of AI. The quality and authenticity of the data is representative of your model. machine learning course in hyderabad

    ReplyDelete
  17. This was nice and amazing and the given contents were very useful and the precision has given here is good.

    Apache Spark Training in Pune
    Spark Training Institute in Pune

    ReplyDelete
  18. This is a fabulous post I seen because of offer it. It is really what I expected to see trust in future you will continue in sharing such a mind boggling post
    business analytics course

    ReplyDelete
  19. I find your opinion quite interesting, but the other day I stumbled upon a completely different advice from another blogger, I need to think that one through, thanks for posting.
    360DigiTMG certification on data analytics

    ReplyDelete
  20. This post is very simple to read and appreciate without leaving any details out. Great work!
    data science courses in noida

    ReplyDelete
  21. nice blog!! i hope you will share a blog on Data Science.
    data science course

    ReplyDelete
  22. Nice article. I liked very much. All the information given by you are really helpful for my research. keep on posting your views.
    data science course in gurgaon

    ReplyDelete
  23. Nice and very informative blog, glad to learn something through you.
    data science course aurangabad

    ReplyDelete
  24. Very wonderful informative article. I appreciated looking at your article. Very wonderful reveal. I would like to twit this on my followers. Many thanks! .

    AWS Training in Hyderabad

    ReplyDelete
  25. This post is very simple to read and appreciate without leaving any details out. Great work!
    data scientist course in aurangabad

    ReplyDelete
  26. Nice post. I'm impressed! Extremely useful information. Thank you and keep up the good work. whatsapp mod

    ReplyDelete
  27. I'm also visiting this site regularly, this web site is really nice and the users are genuinely sharing good thoughts.
    design firm San Francisco

    ReplyDelete
  28. Your work is very good and I appreciate you and hopping for some more informative posts.
    full stack web development course

    ReplyDelete
  29. This post is very simple to read and appreciate without leaving any details out. Great work!
    data analytics courses in aurangabad

    ReplyDelete
  30. Thanks for sharing this great article we appreciate it, we provide instagram reels download freely and unlimited.

    ReplyDelete
  31. Through this post, I understand that your extraordinary data in playing with every one of the pieces was uncommonly helpful. I prompt here I find issues I've been filtering for. You have a shrewd yet charming technique for creating.
    IoT courses

    ReplyDelete

  32. Such an obliging article. Overpowering to investigate this article.I should thank you for the undertakings you had made for outlining this inconceivable article.
    internet of things certification

    ReplyDelete
  33. Such an obliging article. Overpowering to investigate this article.I should thank you for the undertakings you
    IOT Courses

    ReplyDelete
  34. Such an obliging article. Overwhelming to research this article.I ought to thank you for the endeavors you
    Internet of Things Course

    ReplyDelete
  35. Once more all that considered I read it yesterday yet I had a few considerations about it and today I expected to see it thinking about the way things are luxuriously formed.....
    iot certification courses

    ReplyDelete
  36. Such an obliging article. Overwhelming to research this article.I ought to thank you for the endeavors you......
    Internet Of Things Certification Courses

    ReplyDelete
  37. Such an obliging article. Overwhelming to research this article.I ought to thank you for the endeavors you
    internet Of Things Training in Malaysia

    ReplyDelete
  38. Such an obliging article. Overwhelming to research this article.I ought to thank you for the endeavors you
    IOT Courses

    ReplyDelete
  39. As businesses expand and evolve, efficient supply chain management becomes the cornerstone of their success. The SAP HR Training Course in Noida offered by ERP NOIDA presents a golden opportunity for individuals to acquire the skills and knowledge necessary to excel in this dynamic field. With expert guidance, practical experience, and a comprehensive curriculum, participants are poised to become the supply chain leaders of tomorrow. Take the leap with ERP NOIDA and unlock your potential in the world of supply chain management.

    ReplyDelete