Day 2: Aggregate functions & GROUP BY

A. Lesson overview

Activity breakdown

Business scenario. Ridgeway Outdoor’s finance team is preparing the 2025 year-end pack. They need order and sales totals by year, quarter, month, channel and store, with subtotals and a grand total that a reader can trust. Sales managers want to know which customers order most often and which salespeople bring in the most revenue. Every request is a summary: many rows in, a few rows out.

B. Theory explained clearly

Part 1 — Aggregate functions and NULLs

B1. What an aggregate function does

An aggregate function takes a set of values and returns a single value.

Without GROUP BY, an aggregate query treats the whole table (after WHERE) as one group and returns one row:

USE RetailLab;
GO

SELECT  COUNT(*)      AS Customers,
        COUNT(Email)  AS WithEmail,
        COUNT(Phone)  AS WithPhone
FROM    Sales.Customer;

Result: 40, 36, 30. The 4 customers with no email and the 10 with no phone are rows, so COUNT(*) includes them, but they have no value to count, so COUNT(Email) and COUNT(Phone) skip them.

When an aggregate skips NULLs, SSMS may show Warning: Null value is eliminated by an aggregate or other SET operation on the Messages tab. It is information, not an error.

B2. NULLs can change an average

Because AVG divides by the number of non-NULL values, it is not always the same as SUM ÷ COUNT(*):

-- The VALUES clause builds a three-row table on the fly (you will study this syntax on Day 3).
SELECT  AVG(v)              AS AvgIgnoringNull,      -- (10 + 20) / 2 = 15
        SUM(v) / COUNT(*)   AS SumOverAllRows        -- (10 + 20) / 3 = 10
FROM    (VALUES (10), (20), (NULL)) AS t(v);

Neither answer is “right” in general. If NULL means unknown, ignoring it (15) is usually correct. If NULL really means zero, write AVG(COALESCE(v, 0)) to say so.

B3. The integer-average trap

The data type AVG returns depends on its input:

Sales.SalesOrderLine.Quantity is an int, so AVG(Quantity) returns 1 although the true average is about 1.48. Convert first: AVG(CAST(Quantity AS decimal(10,2))). Worked example 2 shows both.

B4. Revenue in this course: “net sales”

Each order line’s value after discount is

Quantity * UnitPrice * (1 - DiscountPct)

The course calls the sum of these values net sales. In SQL Server, the multiplication returns a decimal with four decimal places, so the lessons display totals with CAST(SUM(…) AS decimal(12,2)). Converting a decimal to fewer decimal places rounds (it does not truncate). Finance also asked for one rule: cancelled orders are excluded from sales. Shipping charges are not part of net sales.

Part 2 — Primer: joining orders to order lines

B5. Why you need a join today

Order dates, channels and statuses live in Sales.SalesOrder (the order header). Quantities and prices live in Sales.SalesOrderLine. To answer “net sales by year”, you need columns from both tables at once. Combining tables is the job of a join. Day 8 covers joins in depth; today you need only INNER JOIN.

B6. INNER JOIN syntax

SELECT  o.OrderID,
        o.OrderDate,
        o.SalesChannel,
        l.OrderLineID,
        l.ProductID,
        l.Quantity,
        l.UnitPrice,
        l.DiscountPct
FROM    Sales.SalesOrder     AS o           -- o is a table alias
INNER JOIN Sales.SalesOrderLine AS l        -- l is a table alias
        ON l.OrderID = o.OrderID            -- how rows match
WHERE   o.OrderID = 1001
ORDER BY l.OrderLineID;

Result: 2 rows.

B7. A join changes the “grain” of your rows

Look at the result again: order 1001’s date and channel appear on both rows. After the join, each row represents an order line, not an order. This is the most important idea in today’s lesson:

Consequences for aggregation:

• Counting orders after the join needs COUNT(DISTINCT o.OrderID). COUNT(*) counts lines.
• Summing a header column (for example o.ShippingCost) after the join adds it once per line, which over-states the total for any order with several lines. Sum header values from the header table, not from the joined result. (In this dataset every order charged shipping happens to have a single line, so the total looks right — a coincidence you must not rely on.)

The word for “what one row represents” is grain. Always know the grain of the rows you are aggregating.

B8. Date-part functions used today

Day 12 covers dates thoroughly. Today these three are enough to group by period.

Part 3 — GROUP BY and HAVING

B9. GROUP BY

GROUP BY splits rows into groups that share the same values in the listed columns and returns one row per group.

SELECT  SalesChannel,
        COUNT(*) AS Orders
FROM    Sales.SalesOrder
GROUP BY SalesChannel
ORDER BY SalesChannel;

Result: Online 218, Store 158.

The golden rule. Once a query groups, every expression in the SELECT list must be one of:

1. a column (or expression) listed in GROUP BY,
2. an aggregate function, or
3. a constant.

Otherwise SQL Server cannot know which of the group’s many values to show, and it raises error 8120:

-- Fails: Column 'Sales.SalesOrder.OrderDate' is invalid in the select list because it is
-- not contained in either an aggregate function or the GROUP BY clause.
SELECT  SalesChannel, OrderDate, COUNT(*)
FROM    Sales.SalesOrder
GROUP BY SalesChannel;

Fix it by aggregating the column (MAX(OrderDate) AS LatestOrder) or by grouping by it, depending on the question you are asking.

Other rules:

• All NULLs form one group. Grouping orders by StoreID gives one row for each store and one row with StoreID = NULL for the 218 online orders.
• You can group by an expression, such as YEAR(OrderDate). Repeat the same expression in the SELECT list. You cannot use a SELECT alias in GROUP BY, because GROUP BY is processed before SELECT.
• GROUP BY does not sort. Add ORDER BY.

B10. WHERE filters rows; HAVING filters groups

Extending the logical processing order from Day 1:

• Use WHERE for conditions about a single row: o.OrderStatus <> 'Cancelled'.
• Use HAVING for conditions about a group’s aggregate: HAVING COUNT(*) >= 10.
• An aggregate in WHERE fails with error 147 (An aggregate may not appear in the WHERE clause…): at that stage no groups exist yet.
• A SELECT alias in HAVING fails with Invalid column name, because HAVING runs before SELECT. Repeat the aggregate: HAVING SUM(Quantity) >= 40, not HAVING UnitsSold >= 40. (ORDER BY can use the alias.)
• A row-level condition placed in HAVING instead of WHERE can still work if the column is grouped, but filter rows in WHERE when you can: it is clearer and it removes rows before the work of grouping.

B11. Conditional aggregation

Putting a CASE expression inside an aggregate counts or sums only the rows that meet a condition. It produces side-by-side totals in one pass:

SELECT  YEAR(OrderDate) AS OrderYear,
        COUNT(*)                                             AS AllOrders,
        SUM(CASE WHEN SalesChannel = 'Online' THEN 1 ELSE 0 END) AS OnlineOrders,
        COUNT(CASE WHEN SalesChannel = 'Store' THEN 1 END)      AS StoreOrders
FROM    Sales.SalesOrder
GROUP BY YEAR(OrderDate)
ORDER BY OrderYear;

Both styles work. SUM(CASE … THEN 1 ELSE 0 END) adds ones and zeros. COUNT(CASE … THEN 1 END) relies on the missing ELSE producing NULL, which COUNT ignores. On Day 11 you will compare this technique with PIVOT.

Part 4 — ROLLUP, CUBE, GROUPING SETS and GROUPING()

B12. Grouping sets

A plain GROUP BY a, b produces one level of detail. Reports usually also need subtotals and a grand total. Each level of summary is a grouping set — the list of columns a set of rows is grouped by. () (empty parentheses) is the grouping set for the grand total.

The result is equivalent to running one GROUP BY query per grouping set and stacking the results with UNION ALL, but it is shorter to write and usually reads the data only once.

Points to watch:

• Column order matters for ROLLUP. ROLLUP(Year, Quarter) gives subtotals per year. ROLLUP(Quarter, Year) would give subtotals per quarter number across both years — a different report.
• Duplicates are not merged. GROUPING SETS ((), CUBE(a, b)) returns two grand-total rows.
• Limits. SQL Server allows up to 32 expressions and 4,096 grouping sets in one GROUP BY. You are unlikely to approach this, but CUBE grows quickly: 12 columns already mean 4,096 sets.
• You may see the older form GROUP BY a, b WITH ROLLUP in existing code. It is non-ISO syntax kept for backward compatibility; write GROUP BY ROLLUP(a, b) in new work.

B13. Which NULLs are subtotals? GROUPING() and GROUPING_ID()

In a summary row, every column that has been “rolled up” shows NULL. That creates ambiguity whenever a grouped column can also contain real NULLs. StoreID is NULL for every online order, so in GROUP BY ROLLUP(StoreID) there are two NULL rows: the online orders and the grand total.

For ROLLUP(MonthNo, Channel):

Use these functions to:

• Label summary rows: CASE WHEN GROUPING(o.SalesChannel) = 1 THEN 'All channels' ELSE o.SalesChannel END. Do not use COALESCE(col, 'Total') — it cannot tell a real NULL from a subtotal.
• Sort reliably: ORDER BY GROUPING(col), col puts detail rows before their totals.
• Filter levels in HAVING, for example to drop an unwanted group.

The argument of GROUPING() must match an expression in GROUP BY exactly. If you group by YEAR(o.OrderDate), write GROUPING(YEAR(o.OrderDate)), not GROUPING(OrderYear).

C. Dataset and starting state

Relevant columns

Preparation. None if RetailLab is unchanged. Otherwise run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive: drops and rebuilds RetailLab), or run scripts 00 → 03.

Validation. Expected result: 376, 702, 395, 1038, 124384.4345.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*)      FROM Sales.SalesOrder)     AS Orders,
        (SELECT COUNT(*)      FROM Sales.SalesOrderLine) AS OrderLines,
        (SELECT COUNT(*)      FROM Sales.Payment)        AS Payments,
        (SELECT SUM(Quantity) FROM Sales.SalesOrderLine) AS UnitsSold,
        (SELECT SUM(Quantity * UnitPrice * (1 - DiscountPct))
         FROM Sales.SalesOrderLine)                       AS GrossLineValue;

D. Worked examples

Run USE RetailLab; once in a new query window, then highlight and run each example.

Worked example 1 — Profile the orders table

SELECT  COUNT(*)                   AS Orders,
        COUNT(ShippedDate)         AS OrdersWithShipDate,   -- NULL = not shipped / cancelled
        COUNT(EmployeeID)          AS OrdersWithSalesperson, -- NULL = online order
        COUNT(DISTINCT CustomerID) AS DistinctCustomers,
        MIN(OrderDate)             AS FirstOrder,
        MAX(OrderDate)             AS LastOrder,
        SUM(ShippingCost)          AS ShippingCharged
FROM    Sales.SalesOrder;

Expected result: one row.

What it tells you: 11 orders have no ship date (7 cancelled, 4 pending); 158 store orders have a salesperson; only 35 of the 40 customers have ever ordered. One query, seven facts — profiling a table like this is a good habit before analysing it.

Worked example 2 — The integer-average trap

SELECT  AVG(Quantity)                          AS AvgQtyInteger,   -- int input: fraction lost
        AVG(CAST(Quantity AS decimal(10,2)))   AS AvgQtyDecimal,   -- decimal(38,6) result
        SUM(Quantity)                          AS UnitsSold,
        COUNT(*)                               AS OrderLines
FROM    Sales.SalesOrderLine;

Expected result: 1, 1.478632, 1038, 702. The first column is not rounded — it is truncated. 1038 ÷ 702 = 1.4786…

Worked example 3 — Group by two columns

SELECT  SalesChannel,
        OrderStatus,
        COUNT(*) AS Orders
FROM    Sales.SalesOrder
GROUP BY SalesChannel, OrderStatus
ORDER BY SalesChannel, OrderStatus;

Expected result: 5 rows.

Only combinations that exist in the data appear: there is no Store / Pending row, because no such orders exist. (On Day 8 you will learn how to show missing combinations with a zero.)

Worked example 4 — Net sales by year (join + group)

SELECT  YEAR(o.OrderDate)                                              AS OrderYear,
        COUNT(DISTINCT o.OrderID)                                      AS Orders,      -- orders
        COUNT(*)                                                       AS OrderLines,  -- joined rows
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
             AS decimal(12,2))                                         AS NetSales
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE   o.OrderStatus <> 'Cancelled'           -- row filter: before grouping
GROUP BY YEAR(o.OrderDate)                     -- the expression, not the alias
ORDER BY OrderYear;                            -- ORDER BY may use the alias

Expected result

What to notice: Orders and OrderLines differ because of the grain change (B7). The 7 cancelled orders and their 13 lines are excluded by WHERE before any grouping happens: 369 orders and 689 lines remain. Net sales grew by about 8.8% year on year.

Worked example 5 — HAVING on a single table

Which products have sold at least 40 units across all order lines?

SELECT  ProductID,
        SUM(Quantity) AS UnitsSold
FROM    Sales.SalesOrderLine
GROUP BY ProductID
HAVING  SUM(Quantity) >= 40          -- repeat the aggregate: the alias does not exist yet
ORDER BY UnitsSold DESC, ProductID;  -- the alias is fine here

Expected result: 7 rows — product 9 (84 units), 2 (48), 18 (46), 10 (45), 15 (45), 8 (41) and 1 (40). Product 9 is the gas canister, a cheap consumable bought in multiples. Products 10 and 15 tie on 45 and are ordered by ProductID.

Worked example 6 — HAVING after a join: frequent customers

Which customers have placed 18 or more non-cancelled orders?

SELECT  o.CustomerID,
        COUNT(DISTINCT o.OrderID)                                      AS Orders,
        COUNT(*)                                                       AS OrderLines,
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
             AS decimal(12,2))                                         AS NetSales
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE   o.OrderStatus <> 'Cancelled'
GROUP BY o.CustomerID
HAVING  COUNT(DISTINCT o.OrderID) >= 18
ORDER BY Orders DESC, o.CustomerID;

Expected result: 8 rows.

Try the mistake: change the HAVING to COUNT(*) >= 18. Now the query counts lines, and 13 customers qualify instead of 8 — customer 24, for example, has only 9 orders but 20 order lines. Nothing errors; the answer is simply wrong.

Worked example 7 — ROLLUP: quarterly sales with year subtotals and a grand total

SELECT  YEAR(o.OrderDate)                                              AS OrderYear,
        DATEPART(QUARTER, o.OrderDate)                                 AS OrderQuarter,
        COUNT(DISTINCT o.OrderID)                                      AS Orders,
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
             AS decimal(12,2))                                         AS NetSales
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE   o.OrderStatus <> 'Cancelled'
GROUP BY ROLLUP (YEAR(o.OrderDate), DATEPART(QUARTER, o.OrderDate))
ORDER BY GROUPING(YEAR(o.OrderDate)),               -- grand total last
         OrderYear,
         GROUPING(DATEPART(QUARTER, o.OrderDate)),  -- year subtotal after its quarters
         OrderQuarter;

Expected result: 11 rows (8 quarters + 2 year subtotals + 1 grand total).

What to notice: the subtotal rows have correct order counts because COUNT(DISTINCT …) is recalculated at each level — subtotals are not the sum of the rows above them, they are fresh aggregates over a larger group. Q3 2025 is the weakest 2025 quarter; it includes the August week with no orders.

Worked example 8 — GROUPING() resolves the NULL ambiguity

SELECT  StoreID,
        GROUPING(StoreID) AS IsTotalRow,
        CASE
            WHEN GROUPING(StoreID) = 1 THEN 'All orders'
            WHEN StoreID IS NULL       THEN 'Online (no store)'
            ELSE CONCAT('Store ', StoreID)
        END               AS RowLabel,
        COUNT(*)          AS Orders
FROM    Sales.SalesOrder
GROUP BY ROLLUP (StoreID)
ORDER BY GROUPING(StoreID), StoreID;

Expected result

Two rows show StoreID = NULL. Only GROUPING(StoreID) tells them apart. Replacing the CASE with COALESCE(CAST(StoreID AS varchar(10)), 'All orders') would mislabel 218 online orders as the grand total.

Worked example 9 — CUBE: every combination of channel and year

SELECT  CASE WHEN GROUPING(SalesChannel) = 1 THEN 'All channels' ELSE SalesChannel END AS Channel,
        CASE WHEN GROUPING(YEAR(OrderDate)) = 1 THEN 'All years'
             ELSE CAST(YEAR(OrderDate) AS varchar(4)) END                                AS OrderYear,
        GROUPING_ID(SalesChannel, YEAR(OrderDate))                                       AS GroupingLevel,
        COUNT(*)                                                                         AS Orders
FROM    Sales.SalesOrder
GROUP BY CUBE (SalesChannel, YEAR(OrderDate))
ORDER BY GroupingLevel, Channel, OrderYear;

Expected result: 9 rows (2 × 2 detail + 2 channel totals + 2 year totals + 1 grand total).

GroupingLevel is GROUPING(SalesChannel) * 2 + GROUPING(YEAR(OrderDate)): 0 = detail, 1 = year rolled up, 2 = channel rolled up, 3 = both. Sorting by it groups each level together.

Worked example 10 — GROUPING SETS: two unrelated summaries in one result

Marketing wants customer counts by country and by segment, plus the total, in one table.

SELECT  CASE
            WHEN GROUPING(Country) = 0         THEN 'Country'
            WHEN GROUPING(CustomerSegment) = 0 THEN 'Segment'
            ELSE                                    'Total'
        END                                    AS Dimension,
        COALESCE(Country, CustomerSegment, N'All customers') AS DimensionValue,
        COUNT(*)                               AS Customers
FROM    Sales.Customer
GROUP BY GROUPING SETS ((Country), (CustomerSegment), ())
ORDER BY GROUPING(Country), GROUPING(CustomerSegment), DimensionValue;

Expected result: 13 rows — 9 countries (Denmark 1, France 4, Germany 2, Ireland 4, Italy 1, Spain 1, Sweden 1, United Kingdom 25, United States 1), then 3 segments (Business 8, Consumer 27, VIP 5), then All customers 40.

COALESCE is safe here because neither Country nor CustomerSegment allows NULL, so a NULL can only mean “rolled up”. The Dimension column still uses GROUPING() because that is the reliable test.

E. Guided practical lab — Finance’s 2025 monthly sales by channel

Finance wants 2025 net sales by month, split by channel, with a total for every month and for the year. Build it step by step in one query window.

Step 1 — Establish the rows you will aggregate

1. Do: Join orders to lines, keep 2025 non-cancelled orders, and count.
2. Run:

   USE RetailLab;
   GO
   
   SELECT  COUNT(*)                  AS OrderLines,
           COUNT(DISTINCT o.OrderID) AS Orders,
           CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
   FROM    Sales.SalesOrder     AS o
   JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
   WHERE   o.OrderDate >= '2025-01-01'
     AND   o.OrderDate <  '2026-01-01'
     AND   o.OrderStatus <> 'Cancelled';

3. Observe: 360, 198, 63741.94.
4. Why it matters: This is your control total. Every later version of the report must add up to the same 63,741.94.
5. Confirm: The three values match.

Step 2 — One row per month

1. Do: Add MONTH(o.OrderDate) to the SELECT list and to GROUP BY.
2. Run: replace the SELECT list and add GROUP BY / ORDER BY:

   SELECT  MONTH(o.OrderDate)        AS OrderMonth,
           COUNT(DISTINCT o.OrderID) AS Orders,
           CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
   FROM    Sales.SalesOrder     AS o
   JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
   WHERE   o.OrderDate >= '2025-01-01'
     AND   o.OrderDate <  '2026-01-01'
     AND   o.OrderStatus <> 'Cancelled'
   GROUP BY MONTH(o.OrderDate)
   ORDER BY OrderMonth;

3. Observe: 12 rows. December is the strongest month (27 orders, 9,718.65); August the weakest (10 orders, 2,419.30) — the week of the warehouse system migration.
4. Why it matters: Unusual values are worth explaining before you publish a report.
5. Confirm: 12 rows.

Step 3 — Split by channel

1. Do: Add o.SalesChannel to both the SELECT list and GROUP BY.
2. Run:

   SELECT  MONTH(o.OrderDate)        AS OrderMonth,
           o.SalesChannel,
           COUNT(DISTINCT o.OrderID) AS Orders,
           CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
   FROM    Sales.SalesOrder     AS o
   JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
   WHERE   o.OrderDate >= '2025-01-01'
     AND   o.OrderDate <  '2026-01-01'
     AND   o.OrderStatus <> 'Cancelled'
   GROUP BY MONTH(o.OrderDate), o.SalesChannel
   ORDER BY OrderMonth, o.SalesChannel;

3. Observe: 24 rows: every month has both online and store sales.
4. Why it matters: The grain of the output is now month × channel.
5. Confirm: 24 rows; January Online = 1321.89, January Store = 2427.68.

Step 4 — Add month subtotals and a year total with ROLLUP

1. Do: Wrap the grouping columns in ROLLUP.
2. Run: the same query with only the GROUP BY line changed:

   SELECT  MONTH(o.OrderDate)        AS OrderMonth,
           o.SalesChannel,
           COUNT(DISTINCT o.OrderID) AS Orders,
           CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
   FROM    Sales.SalesOrder     AS o
   JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
   WHERE   o.OrderDate >= '2025-01-01'
     AND   o.OrderDate <  '2026-01-01'
     AND   o.OrderStatus <> 'Cancelled'
   GROUP BY ROLLUP (MONTH(o.OrderDate), o.SalesChannel)
   ORDER BY OrderMonth, o.SalesChannel;

3. Observe: 37 rows (24 detail + 12 month subtotals + 1 grand total). The subtotal rows show NULL for SalesChannel, and the grand total — with NULL for both columns — appears first, because NULL sorts first.
4. Why it matters: The numbers are right, but a reader cannot easily tell which rows are totals. Step 5 fixes that.
5. Confirm: 37 rows; the row with two NULLs shows 198 orders and 63741.94 — your control total from step 1.

Step 5 — Label and order the summary rows

1. Do: Use GROUPING() for labels and ordering.
2. Run: the complete query:

   SELECT  CASE WHEN GROUPING(MONTH(o.OrderDate)) = 1 THEN 'Year 2025'
                ELSE CONCAT('Month ', MONTH(o.OrderDate)) END          AS Period,
           CASE WHEN GROUPING(o.SalesChannel) = 1 THEN 'All channels'
                ELSE o.SalesChannel END                                 AS Channel,
           CASE GROUPING_ID(MONTH(o.OrderDate), o.SalesChannel)
                WHEN 0 THEN 'Detail'
                WHEN 1 THEN 'Month total'
                WHEN 3 THEN 'Year total'
           END                                                          AS RowType,
           COUNT(DISTINCT o.OrderID)                                    AS Orders,
           CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
   FROM    Sales.SalesOrder     AS o
   JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
   WHERE   o.OrderDate >= '2025-01-01'
     AND   o.OrderDate <  '2026-01-01'
     AND   o.OrderStatus <> 'Cancelled'
   GROUP BY ROLLUP (MONTH(o.OrderDate), o.SalesChannel)
   ORDER BY GROUPING(MONTH(o.OrderDate)),
            MONTH(o.OrderDate),
            GROUPING(o.SalesChannel),
            o.SalesChannel;

3. Observe: the first three rows are Month 1 / Online / Detail (5 orders, 1321.89), Month 1 / Store / Detail (8, 2427.68) and Month 1 / All channels / Month total (13, 3749.57). The last row is Year 2025 / All channels / Year total (198, 63741.94).
4. Why it matters: ORDER BY can use GROUPING() to put each total directly under its details. CONCAT converts the month number to text automatically.
5. Confirm: 37 rows; last row = 63741.94.

Step 6 — The same totals, side by side, with conditional aggregation

1. Do: Many readers prefer one row per month with a column per channel. Replace the channel grouping with SUM(CASE …).

2. Run:

   SELECT  CASE WHEN GROUPING(MONTH(o.OrderDate)) = 1 THEN 'Year 2025'
                ELSE CONCAT('Month ', MONTH(o.OrderDate)) END AS Period,
           CAST(SUM(CASE WHEN o.SalesChannel = 'Online'
                         THEN l.Quantity * l.UnitPrice * (1 - l.DiscountPct) ELSE 0 END)
                AS decimal(12,2))                              AS OnlineSales,
           CAST(SUM(CASE WHEN o.SalesChannel = 'Store'
                         THEN l.Quantity * l.UnitPrice * (1 - l.DiscountPct) ELSE 0 END)
                AS decimal(12,2))                              AS StoreSales,
           CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS TotalSales
   FROM    Sales.SalesOrder     AS o
   JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
   WHERE   o.OrderDate >= '2025-01-01'
     AND   o.OrderDate <  '2026-01-01'
     AND   o.OrderStatus <> 'Cancelled'
   GROUP BY ROLLUP (MONTH(o.OrderDate))
   ORDER BY GROUPING(MONTH(o.OrderDate)), MONTH(o.OrderDate);

3. Observe: 13 rows.

   Period	OnlineSales	StoreSales	TotalSales
   Month 1	1321.89	2427.68	3749.57
   Month 2	1428.80	2462.80	3891.60
   Month 3	4079.82	2807.47	6887.29
   Month 4	3164.03	3703.59	6867.62
   Month 5	2878.60	2431.88	5310.48
   Month 6	2994.18	2885.52	5879.70
   Month 7	3774.58	762.00	4536.58
   Month 8	1834.75	584.55	2419.30
   Month 9	3379.14	2375.95	5755.09
   Month 10	2429.77	2208.23	4638.00
   Month 11	1006.51	3081.53	4088.04
   Month 12	6097.85	3620.80	9718.65
   Year 2025	34389.94	29352.01	63741.94

4. Why it matters: The same numbers in a reader-friendly shape. In each row OnlineSales + StoreSales = TotalSales, a quick self-check.

5. Confirm: 13 rows; the Year 2025 row matches the control total.

F. Independent practice

Write each query before looking at section G. Use ORDER BY with a unique tie-breaker.

Task 1 — Price statistics by category (straightforward)

For each CategoryID in Inventory.Product that has at least three products, return CategoryID, Products (count), MinPrice, MaxPrice and AvgPrice (rounded to 2 decimal places). Sort by CategoryID.

Success criteria: 8 rows. Category 2 (tents) has 4 products, min 189.00, max 599.00, average 346.50. Category 10 (navigation) averages 172.00.

Task 2 — Payment summary (COUNT variants)

a) In one row, return the number of payments, the number of distinct orders that have been paid, and the total amount received.

b) Then return one row per PaymentMethod with Payments, TotalAmount, AveragePayment (2 decimal places) and LargestPayment, sorted by TotalAmount from highest to lowest.

Success criteria: (a) 395 payments, 364 paid orders, 120861.55. (b) 4 rows, starting with Card (194 payments, 67025.73) and ending with Gift Card.

Task 3 — Salesperson performance in 2025 (join + HAVING)

For store orders placed in 2025, return one row per EmployeeID with Orders, NetSales and AvgOrderValue (net sales ÷ orders, 2 decimal places). Include only salespeople with at least 10 orders. Sort by NetSales, highest first.

Success criteria: 5 rows. Employee 10 leads with 16 orders, 7786.55 net sales and an average order value of 486.66. Employee 15 (9 orders) is excluded.

Task 4 — One report, two dimensions (challenging)

Finance wants a single 2025 result (non-cancelled orders) with net sales and order counts:

• one row per channel,
• one row per store — store orders only; there must be no row for the online orders’ NULL StoreID,
• one grand total row.

Return RowLabel (Channel: Online, Channel: Store, Store 1, Store 2, Store 3, All 2025 orders), Orders and NetSales. Show channel rows first, then store rows, then the total.

Success criteria: 6 rows. Store rows: 21 / 7124.35, 38 / 12253.09, 29 / 9974.56. Total: 198 / 63741.94. The three store rows add up to the Channel: Store row (88 orders, 29352.01).

Stretch challenge — Channel mix by year (optional)

Using all non-cancelled orders, produce a CUBE of year × channel showing Orders, NetSales and OnlineSharePct (online net sales as a percentage of net sales in that row, 1 decimal place). Label rolled-up values as All years / All channels, add a Level column (Detail, Year total, Channel total, Grand total) using GROUPING_ID, and sort detail rows first, then year totals, channel totals and the grand total.

Success criteria: 9 rows. Year totals: 2024 = 171 orders, 58612.94, 59.2 %; 2025 = 198 orders, 63741.94, 54.0 %. Grand total: 369 orders, 122354.88, 56.5 %.

G. Solutions and explanations

SELECT  CategoryID,
        COUNT(*)                            AS Products,
        MIN(ListPrice)                      AS MinPrice,
        MAX(ListPrice)                      AS MaxPrice,
        CAST(AVG(ListPrice) AS decimal(10,2)) AS AvgPrice
FROM    Inventory.Product
GROUP BY CategoryID
HAVING  COUNT(*) >= 3
ORDER BY CategoryID;

-- (a)
SELECT  COUNT(*)                AS Payments,
        COUNT(DISTINCT OrderID) AS PaidOrders,
        SUM(Amount)             AS TotalReceived
FROM    Sales.Payment;

-- (b)
SELECT  PaymentMethod,
        COUNT(*)                          AS Payments,
        SUM(Amount)                       AS TotalAmount,
        CAST(AVG(Amount) AS decimal(10,2)) AS AveragePayment,
        MAX(Amount)                       AS LargestPayment
FROM    Sales.Payment
GROUP BY PaymentMethod
ORDER BY TotalAmount DESC, PaymentMethod;

SELECT  o.EmployeeID,
        COUNT(DISTINCT o.OrderID)                                      AS Orders,
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
             AS decimal(12,2))                                         AS NetSales,
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
             / COUNT(DISTINCT o.OrderID) AS decimal(10,2))             AS AvgOrderValue
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE   o.SalesChannel = 'Store'
  AND   o.OrderDate >= '2025-01-01'
  AND   o.OrderDate <  '2026-01-01'
  AND   o.OrderStatus <> 'Cancelled'
GROUP BY o.EmployeeID
HAVING  COUNT(DISTINCT o.OrderID) >= 10
ORDER BY NetSales DESC, o.EmployeeID;

SELECT  CASE
            WHEN GROUPING(o.SalesChannel) = 0 THEN CONCAT('Channel: ', o.SalesChannel)
            WHEN GROUPING(o.StoreID) = 0      THEN CONCAT('Store ', o.StoreID)
            ELSE                                   'All 2025 orders'
        END                                                            AS RowLabel,
        COUNT(DISTINCT o.OrderID)                                      AS Orders,
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
             AS decimal(12,2))                                         AS NetSales
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE   o.OrderDate >= '2025-01-01'
  AND   o.OrderDate <  '2026-01-01'
  AND   o.OrderStatus <> 'Cancelled'
GROUP BY GROUPING SETS ((o.SalesChannel), (o.StoreID), ())
HAVING  NOT (GROUPING(o.StoreID) = 0 AND o.StoreID IS NULL)   -- drop the online "store"
ORDER BY GROUPING(o.SalesChannel),
         GROUPING(o.StoreID),
         o.SalesChannel,
         o.StoreID;

SELECT  CASE WHEN GROUPING(YEAR(o.OrderDate)) = 1 THEN 'All years'
             ELSE CAST(YEAR(o.OrderDate) AS varchar(4)) END             AS OrderYear,
        CASE WHEN GROUPING(o.SalesChannel) = 1 THEN 'All channels'
             ELSE o.SalesChannel END                                      AS Channel,
        CASE GROUPING_ID(YEAR(o.OrderDate), o.SalesChannel)
             WHEN 0 THEN 'Detail'
             WHEN 1 THEN 'Year total'
             WHEN 2 THEN 'Channel total'
             WHEN 3 THEN 'Grand total'
        END                                                               AS Level,
        COUNT(DISTINCT o.OrderID)                                         AS Orders,
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
             AS decimal(12,2))                                            AS NetSales,
        CAST(100.0 * SUM(CASE WHEN o.SalesChannel = 'Online'
                              THEN l.Quantity * l.UnitPrice * (1 - l.DiscountPct)
                              ELSE 0 END)
                   / SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
             AS decimal(5,1))                                             AS OnlineSharePct
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE   o.OrderStatus <> 'Cancelled'
GROUP BY CUBE (YEAR(o.OrderDate), o.SalesChannel)
ORDER BY GROUPING_ID(YEAR(o.OrderDate), o.SalesChannel),
         YEAR(o.OrderDate),
         o.SalesChannel;

H. Knowledge check

1. Understanding. SELECT COUNT(*), COUNT(Email) FROM Sales.Customer; returns 40 and 36. Explain the difference.

2. Query interpretation. How many rows does this query return, and what are they?

   SELECT SalesChannel, OrderStatus, COUNT(*)
   FROM   Sales.SalesOrder
   GROUP BY ROLLUP (SalesChannel, OrderStatus);

3. Debugging. Why does this fail, and what are two different ways to fix it?

   SELECT CustomerID, OrderDate, COUNT(*) AS Orders
   FROM   Sales.SalesOrder
   GROUP BY CustomerID;

4. Debugging. A colleague wants customers with more than 20 orders and writes WHERE COUNT(*) > 20 before GROUP BY CustomerID. What happens, and what is the fix?

5. Query interpretation. After joining Sales.SalesOrder to Sales.SalesOrderLine, a report shows customer 12 with “59 orders”. The order table says 27. What went wrong?

I. Troubleshooting and cleanup

Cleanup. Day 2 is read-only and creates no objects. Leave RetailLab unchanged for Day 3. Optionally save your work as day02_practice.sql.

J. Completion checklist, recap and references

Completion checklist

• The validation query in section C returned 376, 702, 395, 1038 and 124384.4345.
• Worked example 4 shows 171 / 58612.94 for 2024 and 198 / 63741.94 for 2025.
• Your guided-lab report has 37 rows (step 5) and 13 rows (step 6), and both end with 63741.94.
• Tasks 1–4 match the success criteria (8, 1 + 4, 5 and 6 rows).
• You can explain, without notes, why COUNT(*) after a join can over-count orders.

Recap

• Aggregates collapse many rows into one value; all except COUNT(*) ignore NULLs. AVG of an integer returns an integer.
• A join changes the grain: after joining orders to lines, each row is a line. Count orders with COUNT(DISTINCT o.OrderID).
• Every non-aggregated SELECT column must appear in GROUP BY. All NULLs form one group. GROUP BY does not sort.
• WHERE filters rows before grouping; HAVING filters groups after aggregation. Neither can use SELECT aliases.
• ROLLUP builds hierarchical subtotals, CUBE every combination, GROUPING SETS exactly the levels you list; () is the grand total.
• GROUPING() and GROUPING_ID() distinguish subtotal NULLs from real NULLs — use them to label, sort and filter summary rows.
• Conditional aggregation (SUM(CASE …)) puts several totals side by side in one row.

Next: Day 3 — Subqueries and derived tables, with a LEFT JOIN primer for finding customers who have never ordered.

References (Microsoft Learn)

• Aggregate functions (Transact-SQL)
• COUNT (Transact-SQL)
• AVG (Transact-SQL) — return types by input type
• SELECT – GROUP BY (Transact-SQL) — ROLLUP, CUBE, GROUPING SETS and their limits
• SELECT – HAVING (Transact-SQL)
• GROUPING (Transact-SQL)
• GROUPING_ID (Transact-SQL)
• FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
• DATEPART (Transact-SQL)
• Precision, scale, and length (Transact-SQL)