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;

43 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. Hadoop training in hyderabad.All the basic and get the full knowledge of hadoop.
    hadoop training in hyderabad

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

    ReplyDelete
  4. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.selenium training in bangalore

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

    Best Software company in New jersey, USA

    ReplyDelete


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

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

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

    Best IT Security Services | Austere Technologies

    ReplyDelete
  9. 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
  10. Great article, really very helpful content you made. Thank you, keep sharing.

    Internet Of Things(‭IOT) Services | Austere Technologies

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

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

    Best chartered accountant courses| Avinash college of commerce

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

    Best Software Testing Services | Austere Technology

    ReplyDelete
  14. Hi Thanks for the nice information its very useful to read your blog. We provide Software Development Services

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

    ReplyDelete
  16. Thanks for sharing a valuable information to us. Thanks for your blog.
    CIMA training institute in Hyderabad | ISFS

    ReplyDelete
  17. Thanks for sharing such an informative blog to us, keep on updating.
    ACCA training institute in Hyderabad | ISFS

    ReplyDelete
  18. Wow...What an excellent informative blog, really helpful. Thank you. Best Oracle DBA Course Training| orskl

    ReplyDelete
  19. 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
  20. 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
  21. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts.Informatica Training in Bangalore

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


    ReplyDelete
  23. Awesome,Thank you so much for sharing such an awesome blog.Big Data Training in Marathahalli

    ReplyDelete
  24. Thanks for sharing this blog. This very important and informative blog.Tableau Training in Bangalore

    ReplyDelete
  25. Learned a lot of new things from your post! Good creation and HATS OFF to the creativity of your mind.Hadoop Training in Bangalore

    ReplyDelete
  26. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge.

    sap abap training in bangalore

    sap abap courses in bangalore

    sap abap classes in bangalore

    sap abap course syllabus

    best sap abap training

    sap abap training center

    sap abap training institute in bangalore

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

    ReplyDelete
  28. 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
  29. It’s really great information for becoming a better Blogger. Keep sharing, Thanks. For more details to visit Best junior colleges in secunderabad

    ReplyDelete