Week 1 · SQL Basics / Day 4
Goal: answer any how-many, how-much, highest, lowest, average question in one query, and know exactly how each function treats NULL.
Read: SQL Aggregate Functions, SQL Count, SQL Sum, SQL Avg, SQL Min and Max.
SELECT COUNT(*) FROM Production.Product;An aggregate function turns many rows into one value. COUNT(*) counts rows, whatever is in them, so it returns all 504 products.
SELECT COUNT(Color) FROM Production.Product;COUNT(column) counts only the rows where that column is not NULL. 248 products have no colour, so the answer is 256, not 504. This is the key rule of the day: every aggregate except COUNT(*) ignores NULL.
COUNT(column) skips NULLs: 504 rows, but only 256 have a colour.
SELECT COUNT(DISTINCT TerritoryID) FROM Sales.SalesOrderHeader;COUNT(DISTINCT column) counts how many different values appear. 31,465 orders are spread across 10 different sales territories.
SELECT SUM(OrderQty) FROM Sales.SalesOrderDetail;SUM adds up a numeric column. SalesOrderDetail has one row per product on an order, so this is the total number of items ever sold: 274,914.
SELECT SUM(OrderQty * UnitPrice) AS Revenue FROM Sales.SalesOrderDetail;SUM can add up a calculation. For every order line, quantity x unit price is worked out first, then all the results are summed: about 110.37 million before discounts.
SELECT SUM(LineTotal) AS Revenue FROM Sales.SalesOrderDetail; -- LineTotal already includes discount. Compare.LineTotal is a computed column equal to quantity x unit price x (1 - discount). The sum is about 109.85 million, roughly 527,508 less than the previous query. The difference is the total discount given to customers. Always check whether a stored total already includes discounts before calculating your own.
LineTotal is about 527,508 lower: that is the total discount given.
SELECT AVG(ListPrice) FROM Production.Product;AVG returns the mean: the sum divided by the number of non-NULL values. Over all 504 products the average price is 438.67.
SELECT AVG(ListPrice) FROM Production.Product WHERE ListPrice > 0; -- zeros drag the average down200 products are components with ListPrice 0. A zero is a real value, so AVG includes it and the average drops. Filtering them out first gives the true average selling price: 727.26. The WHERE runs before the aggregate.
200 components priced 0 pull the average from 727.26 down to 438.67.
SELECT MIN(ListPrice) AS Cheapest, MAX(ListPrice) AS Dearest FROM Production.Product WHERE ListPrice > 0;MIN and MAX return the smallest and largest value. Several aggregates can be calculated in one SELECT, each with its own alias. Cheapest product for sale: 2.29 (the patch kit). Dearest: 3578.27 (the Road-150 bikes).
SELECT MIN(OrderDate) AS FirstOrder, MAX(OrderDate) AS LastOrder FROM Sales.SalesOrderHeader;MIN and MAX work on dates too: the earliest date is the minimum, the latest is the maximum. AdventureWorks orders run from 31 May 2011 to 30 June 2014.
SELECT MIN(FirstName), MAX(FirstName) FROM Person.Person;On text, MIN and MAX use alphabetical order: MIN is the first name alphabetically ('A.'), MAX is the last ('Zoe'). Punctuation sorts before letters, which is why 'A.' beats 'Aaron'. No alias was given, so SSMS shows '(No column name)'.
SELECT AVG(Weight) FROM Production.Product;Only 205 of the 504 products have a weight. AVG ignores the 299 NULLs and divides by 205, giving 74.07: the average weight of products whose weight is known.
SELECT AVG(ISNULL(Weight, 0)) FROM Production.Product;ISNULL(Weight, 0) turns every NULL into 0 before averaging, so AVG now divides by all 504 rows and the answer falls to 30.13. Neither answer is wrong; they answer different questions. In a report, decide whether a missing value should count as zero or be left out.
Same column, two answers: 74.07 ignoring NULLs, 30.13 treating NULL as 0. Decide which one the question really means.
SELECT SUM(TotalDue) AS Revenue2013 FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2013-01-01' AND OrderDate < '2014-01-01';WHERE picks the rows first (orders placed in 2013), then SUM adds up their TotalDue: about 48.97 million. The date range uses the safe pattern from Day 2: start inclusive, next year exclusive.
SELECT COUNT(*) FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 1;The simple way: filter with WHERE, then count. 27,659 orders were placed online.
SELECT SUM(CASE WHEN OnlineOrderFlag = 1 THEN 1 ELSE 0 END) AS OnlineOrders,
SUM(CASE WHEN OnlineOrderFlag = 0 THEN 1 ELSE 0 END) AS StoreOrders
FROM Sales.SalesOrderHeader;CASE gives each row a 1 if it matches and 0 if not; SUM adds up the 1s. This is called conditional aggregation. Its advantage is that several counts appear as columns in one row: 27,659 online and 3,806 store orders, which add up to all 31,465. A WHERE clause can only filter for one condition at a time.
SUM(CASE ...) counts several conditions side by side in one pass over the table.
SELECT COUNT(*), MAX(ListPrice) FROM Production.Product WHERE ListPrice > 100000;No product costs more than 100,000, so no rows pass the WHERE. An aggregate query without GROUP BY still returns exactly one row. COUNT returns 0, because counting nothing is zero. MAX, MIN, SUM and AVG return NULL, because there is no value to report. Code that reads these results must handle that NULL.
No rows match, yet one row comes back: COUNT gives 0, MAX gives NULL.
SELECT Name, MAX(ListPrice) FROM Production.Product;Error 8120. MAX collapses 504 rows into one value, but Name still has 504 different values. SQL Server cannot put 504 names next to one number, so it refuses. Every column in the SELECT must either be inside an aggregate or be listed in GROUP BY. To get the name of the dearest product today, use SELECT TOP 1 Name, ListPrice ... ORDER BY ListPrice DESC (Day 3).
Error 8120: a plain column cannot sit next to an aggregate without GROUP BY.
SELECT AVG(OrderQty) FROM Sales.SalesOrderDetail;OrderQty is an integer column, so AVG returns an integer and throws the decimals away: 2 instead of the real 2.27. There is no warning, which makes this an easy mistake in reports.
SELECT AVG(OrderQty * 1.0) FROM Sales.SalesOrderDetail;Multiplying by 1.0 turns each quantity into a decimal before averaging, so the result keeps its decimals: 2.266079. This is the quickest fix.
SELECT AVG(CAST(OrderQty AS DECIMAL(10,2))) FROM Sales.SalesOrderDetail;CAST to DECIMAL does the same job more explicitly: the reader can see exactly which data type is intended. DECIMAL(10,2) means up to 10 digits, 2 of them after the decimal point.
Same 2.266079 as * 1.0. CAST is the more explicit way to say what you mean.
SELECT SUM(CAST(OrderQty AS BIGINT)) FROM Sales.SalesOrderDetail;SUM of an INT column returns INT, which tops out at about 2.1 billion. Beyond that the query fails with an arithmetic overflow error. Casting to BIGINT first raises the limit to about 9.2 quintillion. Here the total is only 274,914, so both work, but on very large tables this cast prevents failures.
SELECT STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name) FROM Sales.SalesTerritory;STRING_AGG is an aggregate for text: it glues the values from many rows into one string, with ', ' between them. WITHIN GROUP (ORDER BY Name) sets the order, so the 10 territories come out alphabetically: Australia, Canada, Central, ... United Kingdom.
The SSMS grid cuts long text short. The full value is: Australia, Canada, Central, France, Germany, Northeast, Northwest, Southeast, Southwest, United Kingdom.
SELECT COUNT(*), COUNT(1), COUNT(ProductID) FROM Production.Product;All three return 504. COUNT(1) counts a constant 1 per row, which is never NULL, so it behaves like COUNT(*). ProductID is the primary key and can never be NULL, so it gives the same answer too. SQL Server runs all three the same way; COUNT(*) is the clearest to read.
SELECT STDEV(ListPrice), VAR(ListPrice) FROM Production.Product WHERE ListPrice > 0;STDEV (standard deviation) measures how spread out the values are; VAR (variance) is the standard deviation squared. A standard deviation of about 884.82 against an average of 727.26 shows prices vary a lot, from a 2.29 patch kit to 3578.27 bikes.
SELECT
COUNT(*) AS Orders,
SUM(TotalDue) AS Revenue,
AVG(TotalDue) AS AvgOrderValue,
MIN(OrderDate) AS FirstOrder,
MAX(OrderDate) AS LastOrder,
COUNT(DISTINCT CustomerID) AS Customers
FROM Sales.SalesOrderHeader;Many aggregates in one SELECT give a one-row business summary: 31,465 orders, about 123.2 million in revenue, an average order of 3,916, from 31 May 2011 to 30 June 2014, placed by 19,119 different customers. SQL Server reads the table once and calculates them all together.
SELECT COUNT(*) AS NoWeight FROM Production.Product WHERE Weight IS NULL;Filter the missing weights with IS NULL, then count the rows with COUNT(*): 299. COUNT(Weight) would not work here, because it skips exactly those NULL rows and would return 0. Check: 299 + 205 = 504.
SELECT SUM(OrderQty) AS TotalSold FROM Sales.SalesOrderDetail WHERE ProductID = 776;WHERE keeps only the order lines for product 776, then SUM adds their quantities: 634 units.
SELECT CAST(ROUND(AVG(ListPrice), 2) AS DECIMAL(10,2)) AS AvgPriceH
FROM Production.Product WHERE Class = 'H';AVG gives the average for high-class products. ROUND(..., 2) rounds it to 2 decimals, and the CAST to DECIMAL(10,2) also displays it with exactly 2 decimals (money would otherwise show 4). Answer: 1679.50.
SELECT MIN(OrderDate) AS FirstOnline, MAX(OrderDate) AS LastOnline
FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 1;WHERE keeps the online orders, then MIN and MAX give the first and last dates: 31 May 2011 to 30 June 2014, the same range as all orders.
SELECT SUM(CASE WHEN TerritoryID = 1 THEN 1 ELSE 0 END) AS Territory1,
SUM(CASE WHEN TerritoryID = 4 THEN 1 ELSE 0 END) AS Territory4
FROM Sales.SalesOrderHeader;Two counts as two columns in one row is the conditional aggregation pattern from drill 9. Each CASE marks the rows of one territory with 1, and SUM counts them: 4,594 orders in territory 1 (Northwest) and 6,224 in territory 4 (Southwest).
COUNT(*) counts every row. COUNT(column), SUM, AVG, MIN and MAX skip NULLs. With no matching rows, COUNT returns 0 and the others return NULL.