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;