Week 1 · SQL Basics / Day 6
Goal: write the report queries a manager asks for by name, and know the order SQL runs your clauses in.
Read: SQL Group By, SQL Having, SQL Exists, SQL Any All, SQL Case.
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP/OFFSET. Every GROUP BY error is explained by it: WHERE cannot use an aggregate because groups do not exist yet, and GROUP BY cannot use a SELECT alias because SELECT has not run yet.SELECT TerritoryID, COUNT(*) AS Orders
FROM Sales.SalesOrderHeader
GROUP BY TerritoryID
ORDER BY Orders DESC;GROUP BY puts the rows into one group per TerritoryID, and the aggregate (COUNT) is calculated once per group, so you get one row per territory instead of one total. ORDER BY can use the alias Orders because ORDER BY runs after SELECT. Territory 9 (Australia) has the most orders, 6,843.
SELECT Color, COUNT(*) AS Products, AVG(ListPrice) AS AvgPrice
FROM Production.Product WHERE ListPrice > 0 GROUP BY Color;WHERE removes the 0-priced components first, then the remaining products are grouped by colour and each group gets a count and an average. Products with no colour are grouped together in a NULL group, just as DISTINCT did on Day 1. Red products are the most expensive on average, at 1,401.95.
NULL colours form their own group (59 products). GROUP BY treats all NULLs as one value.
SELECT TOP 5 h.CustomerID, SUM(h.TotalDue) AS Revenue
FROM Sales.SalesOrderHeader h GROUP BY h.CustomerID ORDER BY Revenue DESC;Group the orders by customer, sum each customer's TotalDue, sort the totals from highest, and keep the first 5. TOP runs last of all, after the sort. Customer 29818 is the biggest, at 989,184.
SELECT YEAR(OrderDate) AS Yr, MONTH(OrderDate) AS Mth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate) ORDER BY Yr, Mth;You can group by an expression, not just a column. Grouping by both YEAR and MONTH gives one row per calendar month: 38 months from May 2011 to June 2014. Grouping by MONTH alone would wrongly merge January 2012 with January 2013.
SELECT FORMAT(OrderDate, 'yyyy-MM') AS YearMonth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader
GROUP BY FORMAT(OrderDate, 'yyyy-MM') ORDER BY YearMonth;FORMAT turns each date into a 'yyyy-MM' text label and the rows are grouped by that label. The totals match the first query exactly. The label sorts correctly as text because the year comes first. FORMAT is slow on big tables, so the YEAR/MONTH version is usually preferred.
Same 38 rows. The first version is faster; FORMAT is easier to read but slower on large tables.
SELECT TerritoryID, COUNT(*) AS Orders
FROM Sales.SalesOrderHeader
WHERE COUNT(*) > 3000
GROUP BY TerritoryID;Error 147: an aggregate may not appear in the WHERE clause. The logical order explains it: WHERE runs before GROUP BY, so when WHERE is checked the groups do not exist yet and there is nothing to count.
SELECT TerritoryID, COUNT(*) AS Orders
FROM Sales.SalesOrderHeader
GROUP BY TerritoryID HAVING COUNT(*) > 3000;HAVING is the WHERE for groups: it runs after GROUP BY, so it can use aggregates. 5 of the 10 territories have more than 3,000 orders. Note the order of rows is not sorted, because there is no ORDER BY.
WHERE filters rows before grouping. HAVING filters groups after grouping.
SELECT Color, COUNT(*) AS Products, AVG(ListPrice) AS AvgPrice
FROM Production.Product WHERE ListPrice > 0
GROUP BY Color HAVING AVG(ListPrice) > 500 AND COUNT(*) >= 10;HAVING can combine conditions with AND or OR, just like WHERE. Only colours with an average price above 500 and at least 10 products survive: Black, Blue, Red, Silver and Yellow.
SELECT YEAR(OrderDate) AS Yr, OnlineOrderFlag, COUNT(*) AS Orders
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), OnlineOrderFlag ORDER BY Yr, OnlineOrderFlag;With two grouping columns you get one row per combination: 4 years x 2 flags = 8 rows. It shows the business moving online: in 2013, 12,584 online orders against 1,598 store orders.
SELECT TOP 3 h.CustomerID, SUM(h.TotalDue) AS Revenue
FROM Sales.SalesOrderHeader h
WHERE h.OrderDate >= '2013-01-01'
GROUP BY h.CustomerID
HAVING SUM(h.TotalDue) > 50000
ORDER BY Revenue DESC;Read it in the order SQL runs it: FROM takes the orders; WHERE keeps those from 2013 onwards; GROUP BY makes one group per customer; HAVING keeps customers above 50,000; SELECT calculates the columns; ORDER BY sorts by revenue; TOP keeps 3. Customer 29641 leads with 606,820.
Run order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, TOP.
SELECT c.Name AS Category, SUM(d.LineTotal) AS Revenue
FROM Sales.SalesOrderDetail d
JOIN Production.Product p ON d.ProductID = p.ProductID
JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
JOIN Production.ProductCategory c ON s.ProductCategoryID = c.ProductCategoryID
GROUP BY c.Name ORDER BY Revenue DESC;The joins attach a category name to every order line, then GROUP BY adds up LineTotal per category. Bikes bring in 94.65 million, far ahead of Components, Clothing and Accessories.
-- Check one by hand: Accessories (category 4) without GROUP BY
SELECT SUM(d.LineTotal) AS AccessoriesRevenue
FROM Sales.SalesOrderDetail d
WHERE d.ProductID IN (SELECT p.ProductID FROM Production.Product p
JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.ProductCategoryID = 4);A different route to the same number: a subquery lists every Accessories product, and the outer query sums their order lines with no GROUP BY. It returns exactly 1,272,072.88, matching the Accessories row of the grouped query. Checking one group by hand is the quickest way to trust a report.
1,272,072.88: exactly the Accessories row above, so the grouped query is right.
SELECT p.ProductID FROM Production.Product p
LEFT JOIN Sales.SalesOrderDetail d ON p.ProductID = d.ProductID
WHERE d.ProductID IS NULL;Way 1, the anti-join from Day 5: keep the products whose LEFT JOIN found no order line. 238 products.
SELECT p.ProductID FROM Production.Product p
WHERE NOT EXISTS (SELECT 1 FROM Sales.SalesOrderDetail d WHERE d.ProductID = p.ProductID);Way 2, NOT EXISTS. For each product, the subquery looks for an order line with that ProductID; NOT EXISTS keeps the product if none is found. SELECT 1 is a convention: EXISTS only checks whether a row exists, not what it contains. This is a correlated subquery, because it refers to p from the outer query. Also 238.
SELECT p.ProductID FROM Production.Product p
WHERE p.ProductID NOT IN (SELECT ProductID FROM Sales.SalesOrderDetail);
-- NOT IN breaks silently if the subquery ever returns a NULL. Prefer NOT EXISTS.Way 3, NOT IN against a list from a subquery. Also 238, but risky: if the list ever contains a NULL, 'x NOT IN (..., NULL)' can never be true, so the query silently returns no rows at all. ProductID in SalesOrderDetail cannot be NULL, so it is safe here, but NOT EXISTS is the safe habit.
All three give 238. But if the subquery ever returned a NULL, NOT IN would return 0 rows with no error.
SELECT Name, ListPrice FROM Production.Product
WHERE ListPrice > (SELECT AVG(ListPrice) FROM Production.Product WHERE ListPrice > 0);A scalar subquery returns one value (the average selling price, 727.26), which the outer WHERE compares against. You cannot write WHERE ListPrice > AVG(ListPrice) directly (error 147 again); the subquery calculates the average first. 112 products are above it.
SELECT * FROM (
SELECT CustomerID, COUNT(*) AS Orders FROM Sales.SalesOrderHeader GROUP BY CustomerID
) t WHERE t.Orders > 20;A subquery in FROM is a derived table: its result is used like a temporary table, and it must have an alias (t). The inner query counts orders per customer; the outer WHERE can then filter on that count like an ordinary column. 14 customers have more than 20 orders.
WITH CustomerOrders AS (
SELECT CustomerID, COUNT(*) AS Orders FROM Sales.SalesOrderHeader GROUP BY CustomerID
)
SELECT * FROM CustomerOrders WHERE Orders > 20 ORDER BY Orders DESC;A CTE (common table expression) moves the subquery to the top and gives it a name with WITH name AS (...). The main query then reads from it like a table. Same 14 customers. CTEs read top to bottom, can be referenced more than once, and are the usual style in team code.
SELECT p.Name, p.ProductSubcategoryID, p.ListPrice
FROM Production.Product p
WHERE p.ListPrice = (SELECT MAX(ListPrice) FROM Production.Product x
WHERE x.ProductSubcategoryID = p.ProductSubcategoryID);For each product, the correlated subquery finds the highest price in that product's subcategory, and the product is kept if it matches. The result has 94 rows for 37 subcategories because of ties: three Sport-100 helmets share the top helmet price, for example. Products with a NULL subcategory never match, since x.ProductSubcategoryID = NULL is never true.
94 rows, not 37: every product that ties for the top price is kept, and NULL-subcategory products are skipped.
WITH Ranked AS (
SELECT Name, ProductSubcategoryID, ListPrice,
ROW_NUMBER() OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice DESC) AS rn
FROM Production.Product
)
SELECT * FROM Ranked WHERE rn = 1;ROW_NUMBER() numbers the rows 1, 2, 3... within each partition (subcategory), most expensive first. Unlike GROUP BY, a window function keeps every row and adds a column. Keeping rn = 1 gives exactly one product per subcategory: 38 rows, including one for the NULL group. With ties, one product is picked arbitrarily; window functions are covered properly later.
Exactly one row per subcategory (38 including the NULL group). Ties are broken arbitrarily.
SELECT CASE WHEN ListPrice < 100 THEN 'Budget' WHEN ListPrice < 1000 THEN 'Mid'
ELSE 'Premium' END AS Band,
COUNT(*) AS Products
FROM Production.Product WHERE ListPrice > 0
GROUP BY CASE WHEN ListPrice < 100 THEN 'Budget' WHEN ListPrice < 1000 THEN 'Mid'
ELSE 'Premium' END;CASE creates a label for each product and the rows are grouped by that label: 90 Budget, 128 Mid, 86 Premium. The whole CASE has to be repeated in GROUP BY, because GROUP BY runs before SELECT and cannot see the alias Band. Wrapping the CASE in a CTE avoids the repetition.
GROUP BY Band would fail: GROUP BY runs before SELECT, so the alias does not exist yet.
SELECT t.Name AS Territory,
SUM(CASE WHEN YEAR(h.OrderDate) = 2012 THEN h.TotalDue ELSE 0 END) AS Rev2012,
SUM(CASE WHEN YEAR(h.OrderDate) = 2013 THEN h.TotalDue ELSE 0 END) AS Rev2013,
SUM(CASE WHEN YEAR(h.OrderDate) = 2014 THEN h.TotalDue ELSE 0 END) AS Rev2014
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesTerritory t ON h.TerritoryID = t.TerritoryID
GROUP BY t.Name ORDER BY t.Name;Conditional aggregation from Day 4, now per group. Each CASE passes TotalDue through only for one year (0 otherwise), so each SUM becomes one column. The result is a pivot table: territories down the side, years across the top. 2014 is lower everywhere because the data stops at the end of June 2014.
SELECT t.CountryRegionCode, t.Name, SUM(h.TotalDue) AS Revenue
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesTerritory t ON h.TerritoryID = t.TerritoryID
GROUP BY ROLLUP(t.CountryRegionCode, t.Name);ROLLUP adds extra summary rows to a normal GROUP BY: a subtotal for each country (shown with Name as NULL) and a grand total at the end (both columns NULL, 123.2 million). 10 territory rows + 6 country subtotals + 1 grand total = 17 rows. For single-territory countries the subtotal equals the territory row.
17 rows: 10 territories + 6 country subtotals (Name is NULL) + 1 grand total (both NULL).
SELECT t.Name AS Territory, AVG(h.TotalDue) AS AvgOrderValue
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesTerritory t ON h.TerritoryID = t.TerritoryID
WHERE h.OnlineOrderFlag = 1
GROUP BY t.Name
ORDER BY AvgOrderValue DESC;WHERE keeps the online orders before grouping, then AVG works per territory name. Australian online customers spend the most per order (1,490.38); Central the least (368.44).
SELECT s.Name AS Subcategory, COUNT(*) AS Products
FROM Production.Product p
JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
GROUP BY s.Name
HAVING COUNT(*) > 20
ORDER BY Products DESC;Join for the subcategory name, group by it, and use HAVING to keep the groups with more than 20 products: 5 subcategories, led by Road Bikes with 43.
SELECT TOP 1 h.CustomerID, st.Name AS Store,
pp.FirstName + ' ' + pp.LastName AS ContactPerson,
SUM(h.TotalDue) AS Revenue
FROM Sales.SalesOrderHeader h
JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
LEFT JOIN Sales.Store st ON c.StoreID = st.BusinessEntityID
LEFT JOIN Person.Person pp ON c.PersonID = pp.BusinessEntityID
GROUP BY h.CustomerID, st.Name, pp.FirstName, pp.LastName
ORDER BY Revenue DESC;Group the orders by customer, sum the revenue, and keep the top 1. The name columns must also be in GROUP BY (or inside an aggregate), otherwise you get error 8120 from Day 4. Customer 29818 turns out to be a store, Brakes and Gears, whose contact person is Roger Harui. Both are LEFT JOINs, so the query would also work for an individual customer with no store.
The top customer is a store, Brakes and Gears (contact: Roger Harui), with 989,184.
SELECT MONTH(OrderDate) AS Mth, DATENAME(month, MIN(OrderDate)) AS MonthName,
SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2013-01-01' AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate)
HAVING SUM(TotalDue) < 2000000
ORDER BY Mth;WHERE limits to 2013, GROUP BY makes one group per month, and HAVING keeps months under 2 million. The result is empty, which is a valid answer. DATENAME(month, MIN(OrderDate)) is used for the month name because a plain OrderDate is not allowed next to GROUP BY MONTH(OrderDate).
0 rows is the correct answer: no month in 2013 fell below 2 million.
-- Proof: every month of 2013, lowest first
SELECT MONTH(OrderDate) AS Mth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2013-01-01' AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate)
ORDER BY Revenue;When a query returns nothing, prove it by removing the HAVING. The lowest month of 2013 is January at 2.34 million, still above 2 million, so the empty result above is right.
SELECT p.ProductID, p.Name, ISNULL(SUM(d.OrderQty), 0) AS QtySold
FROM Production.Product p
LEFT JOIN Sales.SalesOrderDetail d ON p.ProductID = d.ProductID
GROUP BY p.ProductID, p.Name
ORDER BY QtySold DESC;LEFT JOIN keeps all 504 products. For the 238 never sold, SUM(d.OrderQty) is NULL, and ISNULL turns it into 0. Grouping by ProductID as well as Name keeps two products with the same name separate. The AWC Logo Cap is the best seller with 8,311 units.