10  Joining and Summarizing Data in SQL

Learning Objectives

After completing this chapter, you will be able to:

  1. Join tables with INNER JOIN on their logical keys, qualifying every column, and test that the keys match before relying on the result.
  2. Use LEFT JOIN to keep rows that have no match, and find the records that have none with anti-joins and NOT EXISTS.
  3. Summarize rows with aggregate functions, GROUP BY, and HAVING, grouping by key rather than by name, use them to profile a table before relying on it, and explain the order in which a query’s clauses are evaluated.
  4. Recognize fan-out when a join repeats a header’s amount on every line, and avoid the double counting it causes.
  5. Write subqueries in the SELECT, WHERE, and FROM clauses, including correlated subqueries, to answer questions that take more than one step.
  6. Build a trial balance from the general ledger, tie a summary to the ledger, and compare the hours a plant records with the hours its standards allow.
Chapter slides View slides Download PowerPoint

Opening Scenario

Your queries in Chapter 9 gave the controller a first look at the manufacturing variance. It is recorded when a work order closes, and in the largest closes of fiscal 2026 most of it is overhead. She comes back to you, her staff accountant, with three follow-up questions. Which products carry the variance? How much of it arose in each of the three years? And is it a matter of volume, with the plant making fewer units over which to spread its costs, or of the cost itself?

Answering them together takes both summaries and joins. The product is recorded on the work order, not on its close. The hours are recorded in the labor time entries and the payroll, and the output in the production completions. The ledger holds the amounts, but it identifies accounts only by their AccountID. Answering the controller means joining these tables on the keys you studied in Chapter 3 and summarizing thousands of their rows into a few totals. This chapter teaches both skills, and it ends by narrowing the answer to her third question.

10.1 Why Join Tables

A relational database stores each fact once, in the table where it belongs, and links the tables through keys. A work order close records its variance and the WorkOrderID of the work order it closes. The work order records the ItemID of the item it makes, and the Item table records the item’s name and group. To see the variance by product group, you must follow those links, from the close to the work order and from the work order to the item. In SQL, following a link is called a join.

You have met the idea twice already. Chapter 3 introduced primary keys, foreign keys, and the cardinality of each relationship, and Chapter 5 used Power Query’s Merge command to combine the invoice lines with the invoices, items, and customers. A join is the SQL version of that merge. It matches rows from two tables whose key values are equal and returns a result that combines columns from both.

One point from Chapter 3 matters more here than anywhere else. The Charles River database declares no foreign keys, so nothing guarantees that every WorkOrderID in WorkOrderClose matches a work order. The relationships are logical and must be tested. A join on keys that do not match does not fail with an error. It simply returns fewer rows, or more, than you expected. So for each join, decide how many matches each starting row should have, check that the key on the other side is unique, and compare the rows and totals before and after the join.

NoteConnecting the Dots

Power Query’s Merge offered several join kinds in Chapter 5. Inner keeps only the rows that match, Left Outer keeps every row of the first query, and Left Anti keeps the rows of the first query that have no match. They are the inner join, the left outer join, and the anti-join of this chapter. XLOOKUP, which returns one matching value for each row, behaves like a left join that brings back a single column, as long as the key it looks up is unique: where the key repeats, XLOOKUP still returns one match, while a join returns a row for each.

10.2 Inner Joins

An inner join returns one row for each pair of rows, one from each table, whose keys match. You write it in the FROM clause, with INNER JOIN and an ON condition that names the matching columns. JOIN written alone means the same thing.

SELECT woc.WorkOrderCloseID, woc.CloseDate, wo.WorkOrderNumber,
    wo.ItemID, woc.TotalVarianceAmount
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID;

The short names after AS are table aliases, and a column written with an alias and a period, as in woc.CloseDate, is qualified: it names the table it comes from. A column whose name appears in both tables must be qualified. WorkOrderID is a column of both WorkOrderClose and WorkOrder, so a query that asks for WorkOrderID without saying which table it comes from stops with the message ambiguous column name: WorkOrderID. The full table name also qualifies a column, but an alias keeps the query short, and it is required when the same table appears twice in one query, a self-join that Chapter 12 uses. The queries in this book qualify every column in a join, even when its name is unique, so that a reader can see where each one comes from.

A join can continue through several tables, one INNER JOIN at a time. Adding the Item table brings the item’s code, name, and group onto each close:

SELECT woc.WorkOrderCloseID, woc.CloseDate, i.ItemCode, i.ItemName,
    i.ItemGroup, woc.TotalVarianceAmount
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID
    INNER JOIN Item AS i ON i.ItemID = wo.ItemID;

Because every close belongs to exactly one work order, and every work order to exactly one item, this query should return exactly as many rows as the WorkOrderClose table holds. If it returns fewer, some closes point to a work order or an item that does not exist, and the inner join has dropped them without a word. WorkOrderID and ItemID are the primary keys of their tables, so no close can be repeated, and an unchanged row count shows that none was lost. On a column that is not unique, an equal count alone would not show that, because a lost row and a repeated one cancel.

Figure 10.1 shows how an inner join matches rows, using four work orders, three of them closed. The inner join returns the three that have a close. The fourth, which has not closed, has no match and does not appear. The last two results in the figure show the left join and the anti-join, which the chapter turns to shortly.

Four Charles River work orders on the left and the work order closes on the right, three of the work orders with a close and one without. The upper result, an inner join on WorkOrderID, holds three rows, one for each work order with a close. The next result, a left join from WorkOrder, holds all four work orders, and the one without a close shows NULL in the close columns. The last result, an anti-join, keeps only that work order.
Figure 10.1: How Inner and Left Joins Match Rows.

10.3 Following the Keys

The questions of this Part reach beyond the sales and ledger tables of Part II, into the Procure-to-Pay and Manufacturing groups and the payroll. Chapter 3 described the Manufacturing group and introduced the others, and Figure 3.8 traced the tables of the manufacturing path, from the bill of materials to the close of a work order. Table 10.1 lists the joins you need to move through them. Each row names two tables and the key that links them, from the table that holds the foreign key to the table whose primary key it refers to.

Table 10.1: Join paths through the purchasing, manufacturing, payroll, and ledger tables
From To Join on
PurchaseOrder Supplier SupplierID
PurchaseOrder Employee, as creator CreatedByEmployeeID = EmployeeID
PurchaseOrder Employee, as approver ApprovedByEmployeeID = EmployeeID
PurchaseOrderLine PurchaseOrder PurchaseOrderID
GoodsReceiptLine PurchaseOrderLine POLineID
PurchaseInvoiceLine PurchaseInvoice PurchaseInvoiceID
PurchaseInvoiceLine PurchaseOrderLine POLineID
PurchaseInvoiceLine GoodsReceiptLine GoodsReceiptLineID
PurchaseInvoice Supplier SupplierID
DisbursementPayment PurchaseInvoice PurchaseInvoiceID
DisbursementPayment Supplier SupplierID
WorkOrder Item ItemID
WorkOrderOperation WorkOrder WorkOrderID
WorkOrderOperation WorkCenter WorkCenterID
WorkOrderClose WorkOrder WorkOrderID
ProductionCompletionLine ProductionCompletion ProductionCompletionID
ProductionCompletionLine Item ItemID
LaborTimeEntry WorkOrderOperation WorkOrderOperationID
TimeClockEntry, LaborTimeEntry Employee EmployeeID
PayrollRegisterLine PayrollRegister PayrollRegisterID
PayrollRegister PayrollPeriod PayrollPeriodID
PayrollRegister Employee, CostCenter EmployeeID, CostCenterID
PayrollPayment PayrollRegister PayrollRegisterID
GLEntry Account, CostCenter AccountID, CostCenterID
GLEntry A source document table SourceDocumentType and SourceDocumentID

Where a table refers to another twice, as a purchase order names both the employee who created it and the one who approved it, each role is a join of its own, and showing both people takes two aliases of Employee. The last row is different from the others. As Chapter 3 explained, the ledger links to its documents through two columns. SourceDocumentType names the kind of document, which for most kinds is a table, and SourceDocumentID holds the key of the document in that table. PayrollSummary is the exception, a summary of a pay period by cost center whose SourceDocumentID is a code, not the key of a table. The ID alone is not enough, because each document table numbers its own rows. The ledger rows whose SourceDocumentID is 7947, for example, come from several kinds of document, of which sales invoice SI-2025-007947, whose revenue posting Figure 1.6 showed and Tutorial 3.2 traced, is only one. A join to a document table therefore names the document type as part of its condition:

SELECT gl.GLEntryID, gl.PostingDate, je.EntryNumber, je.EntryType
FROM GLEntry AS gl
    INNER JOIN JournalEntry AS je
        ON gl.SourceDocumentType = 'JournalEntry'
        AND je.JournalEntryID = gl.SourceDocumentID
WHERE gl.AccountID = 93 AND gl.FiscalYear = 2026;

The query returns the one journal entry posted to the variance account in fiscal 2026, the closing entry you met in Chapter 9. Without the condition on SourceDocumentType, the join would also pair ledger rows from other documents with whichever journal entry happens to share their number.

10.4 Keeping Unmatched Rows with LEFT JOIN

An inner join keeps only matched rows. A left join, or left outer join, keeps every row of the first table, the one named in FROM, and adds the columns of the second table where a match exists. Where none exists, those columns are NULL, as the left join in Figure 10.1 shows. Left joins answer two kinds of question. The first is a complete list, such as every manufactured item with the work orders it has, including the items that have none. The second is an exception list: the rows that have no match at all.

The second kind is called an anti-join. You write a left join and keep only the rows where a column from the second table is NULL, as the last result in Figure 10.1 does. The column must be one that is never NULL in a matched row, such as the second table’s key:

SELECT i.ItemCode, i.ItemName, i.ItemGroup
FROM Item AS i
    LEFT JOIN WorkOrder AS wo ON wo.ItemID = i.ItemID
WHERE i.SupplyMode = 'Manufactured' AND wo.WorkOrderID IS NULL;

The query returns a handful of manufactured items for which the data hold no work order, all of them discontinued. The same test can be written with NOT EXISTS, which keeps a row of the first table when a subquery finds no matching row in the second:

SELECT i.ItemCode, i.ItemName, i.ItemGroup
FROM Item AS i
WHERE i.SupplyMode = 'Manufactured'
    AND NOT EXISTS (
        SELECT 1
        FROM WorkOrder AS wo
        WHERE wo.ItemID = i.ItemID);

The subquery refers to i.ItemID from the outer query, so it is evaluated for each item. That makes it a correlated subquery, a kind that the section on subqueries returns to.

NOT IN looks like a third way to write the test, but it fails when its list contains a NULL. Only a few employees appear as approvers of purchase requisitions in these records, and a few requisitions have no approver at all. The query below uses COUNT(*), which counts the rows a query keeps and which the section on aggregate functions explains. It should count every employee who approved no requisition in these records, yet it returns zero:

SELECT COUNT(*) AS NeverApproved
FROM Employee
WHERE EmployeeID NOT IN (
    SELECT ApprovedByEmployeeID FROM PurchaseRequisition);

For an employee who appears as an approver, NOT IN is false. For every other employee, the NULL in the list makes it unknown, because the employee cannot be shown to differ from a missing value. WHERE keeps only the rows for which its condition is true, so no employee is counted. NOT EXISTS and the anti-join compare only the rows that match, and both find the employees with no approved requisition in these records, nearly all of them. Use either of them, not NOT IN, when the second table’s column can hold a NULL.

WarningWatch out

A condition on the second table placed in the WHERE clause turns a left join into an inner join. The condition wo.Status = 'Closed' in WHERE removes every item without a work order, because a NULL Status never equals Closed. To keep those items, move the condition into the ON clause, where it limits which work orders are matched:

SELECT i.ItemCode, wo.WorkOrderNumber
FROM Item AS i
    LEFT JOIN WorkOrder AS wo
        ON wo.ItemID = i.ItemID AND wo.Status = 'Closed'
WHERE i.SupplyMode = 'Manufactured';

The rule applies to conditions that a NULL cannot satisfy. The IS NULL test of an anti-join is the exception, and it must stay in WHERE.

NoteConnecting the Dots

Chapter 3 warned that a list of customers built by joining to their orders can silently leave out the customers who have never ordered. That is the difference between an inner join and a left join. When the question is about every member of a population, such as every customer, item, or account, start from the table that holds the whole population and left join the others to it.

TipIn Practice

Anti-joins are among the most useful tests in accounting data, because they find what is missing. An existence test starts from the ledger and asks whether every posting points to a document; a completeness test starts from the documents and asks whether every one was posted. Invoices never posted, ledger rows that point to no document, and customers with no order in the records are all anti-join results, and Chapter 12 builds a library of such tests.

10.5 Summarizing with Aggregate Functions

An aggregate function calculates one value from many rows. Table 10.2 lists the ones you will use most. Without a GROUP BY clause, an aggregate summarizes every row the query keeps, so the query SELECT COUNT(*) FROM WorkOrderClose returns a single row with the number of closes. GROUP_CONCAT is the aggregate in the table that joins a group’s values into one text: it lists them in one cell, in no guaranteed order, which makes a short list of exceptions easy to read.

Table 10.2: Aggregate functions and how they treat missing values
Function Returns Treatment of NULL
COUNT(*) The number of rows Counts every row
COUNT(column) The number of values in a column Skips NULLs
COUNT(DISTINCT column) The number of different values Skips NULLs
SUM(column) The total Skips NULLs
AVG(column) The average Skips NULLs, so they do not count as zero
MIN(column), MAX(column) The smallest and largest values Skip NULLs
GROUP_CONCAT(column, sep) The values joined into one text, separated by sep, or by commas if sep is left out Skips NULLs

The GROUP BY clause divides the rows into groups that share the same values in the columns you name, and it calculates each aggregate once per group. This query totals the variance of fiscal 2026 by item group:

SELECT i.ItemGroup, COUNT(*) AS Closes,
    ROUND(SUM(woc.TotalVarianceAmount), 2) AS TotalVariance
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID
    INNER JOIN Item AS i ON i.ItemID = wo.ItemID
WHERE woc.CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY i.ItemGroup
ORDER BY TotalVariance DESC;

The result has one row for each item group, as Figure 10.2 illustrates. In this book, every column in the SELECT list is either named in GROUP BY or wrapped in an aggregate function. A column that is neither has, in general, no single value for the group, and most database systems reject the query unless they can tell that the grouped columns determine it. SQLite does not: it accepts such a bare column and fills it with the value from one of the group’s rows (SQLite, n.d.-a, n.d.-b). That value says nothing about the group as a whole, so treat such a column as an error even though SQLite accepts it.

On the left, several Charles River work order closes of fiscal 2026 with their item group and total variance, some from Furniture and some from Lighting. Arrows gather the rows of each group into one row of the result on the right, which shows each item group with the number of closes and the sum of their variances.
Figure 10.2: GROUP BY Collapses Rows into One Row per Group.

You can group by more than one column, and by a calculated value as well as a column. The year of a close is not stored in its own column, but SUBSTR(woc.CloseDate, 1, 4), from Table 9.2, gives it. With the alias CloseYear in the SELECT list, GROUP BY i.ItemGroup, CloseYear produces one row for each combination of group and year, a use of an alias that the section on evaluation order explains. An aggregate can also total a calculation: SUM(pcl.QuantityCompleted * i.StandardLaborHoursPerUnit) multiplies the two columns in each row and then adds the products.

Two details of aggregates matter after a left join. COUNT(*) counts one row for an unmatched item, because the item’s row is still there, while COUNT(wo.WorkOrderID) counts only the matches and returns zero for an item without work orders, which is usually what you mean. And a SUM over rows that hold only NULLs is itself NULL, not zero, so a complete list with totals needs COALESCE, from Chapter 9, to show a zero: COALESCE(SUM(x), 0).

NoteConnecting the Dots

GROUP BY does what the Rows area of a PivotTable does, and what Power Query’s Group By did in Chapter 6: each combination of the grouping columns becomes one row, and each aggregate becomes one column. One difference is worth noticing. A PivotTable’s calculated field works on the sums of its fields, while an aggregate of an expression, such as the SUM above, calculates the expression in every row first and then adds the results.

WarningWatch out

Group by the key, not by the name. Two different Charles River customers are called Brown LLC, and several items share their names with other items. A query that groups by CustomerName combines the orders of both Brown LLCs into one row, and nothing in the result shows that it has done so. Group by CustomerID, and include the name in the GROUP BY clause as well if you want to show it.

10.6 Filtering Groups with HAVING

WHERE filters rows before they are grouped. HAVING filters the groups after the aggregates are calculated, so its condition can use them. To list only the accounts whose balance at the end of fiscal 2026 is not zero, you group the ledger rows by account and keep the groups whose total is not zero:

SELECT gl.AccountID, ROUND(SUM(gl.Debit) - SUM(gl.Credit), 2) AS Balance
FROM GLEntry AS gl
WHERE gl.PostingDate <= '2026-12-31'
GROUP BY gl.AccountID
HAVING ROUND(SUM(gl.Debit) - SUM(gl.Credit), 2) <> 0;

An aggregate cannot appear in a WHERE clause, because WHERE runs before any group exists. A query that tries it, such as one with WHERE COUNT(*) > 5, stops with an error such as misuse of aggregate: COUNT(). When a condition concerns single rows, such as a date range, it belongs in WHERE. When it concerns a group’s total, count, or average, it belongs in HAVING. The two often work together: in Tutorial 10.1, WHERE keeps the closes of fiscal 2026, and HAVING then keeps the items whose total variance passes a threshold. Filtering rows in WHERE can also save work, because fewer rows reach the grouping step.

10.7 Profiling a Table Before Using It

The aggregates also tell you what a table holds before you rely on it. A data profile is a short set of queries that describes one table: how many rows it has, how many of them fill in the columns you need, whether its keys are unique, which values its code columns take, and which dates it covers. Chapter 2 organized data quality into four dimensions and listed common tests for each in Table 2.3. Table 10.3 gives the SQL for the same tests.

Table 10.3: Data quality tests in SQL
Dimension Tests from Chapter 2 SQL techniques
Accuracy Values in a plausible range; dates in a logical order; no duplicate records MIN and MAX of amounts; a WHERE condition that compares two dates; GROUP BY the key with HAVING COUNT(*) > 1
Completeness Blank values in required fields; entries in every period; every expected type present COUNT(column) against COUNT(*); COUNT(*) by month; GROUP BY a type column
Consistency Related fields that agree; references that match a parent record; identical coding GROUP BY two related columns; an anti-join to the parent table; the distinct values of a code column
Timeliness Date range of the extract; partial first and last periods; entries outside the period MIN and MAX of the dates; COUNT(*) by month at each end

A profile of the WorkOrder table starts with one query:

SELECT COUNT(*) AS WorkOrders,
    COUNT(DISTINCT WorkOrderNumber) AS Numbers,
    COUNT(ClosedDate) AS WithClosedDate,
    MIN(ReleasedDate) AS FirstReleased,
    MAX(ReleasedDate) AS LastReleased
FROM WorkOrder;

It returns one row. The number of distinct work order numbers equals the number of work orders, so no number is used twice. A few hundred work orders have no ClosedDate, the open work orders you listed in Chapter 9, and the release dates run from the first week of January 2024 to the last day of fiscal 2026. A second query checks that two related columns agree:

SELECT Status, COUNT(*) AS WorkOrders,
    COUNT(ClosedDate) AS WithClosedDate
FROM WorkOrder
GROUP BY Status;

Status takes four values, and every Closed work order has a ClosedDate while no other work order has one, so the status and the date tell the same story. None of these results is a problem, and that is itself a finding, as Chapter 2 put it: it tells you which parts of the table you can rely on. Record the profile with the script, in its header or beside the queries, so that a reviewer can see what you checked. Tutorial 10.3 profiles the two tables it compares before dividing one by the other, and Chapter 12 profiles the time records before testing them.

10.8 Guided Tutorial 10.1: Joining the Variance to Its Work Orders and Products

Context and objective. The WorkOrderClose table records each variance but not the product that produced it. In this tutorial, you will join the closes to their work orders and items, test that every close finds its match, and summarize the variance by item group and year. You will check the total against the ledger, keep the items that carry the most variance with HAVING, and list every manufactured item with a left join, including the items with no closes.

Prerequisites. Complete Chapter 9, and open CharlesRiver_Work.sqlite in DB Browser for SQLite on the Execute SQL tab. If Chapter09.sql is still open in the editor, click New tab (Ctrl+T) for an empty editor tab. Start a new script for the chapter with the header comment below, with your own name and the date, and click Save SQL file to save it as Chapter10.sql beside your working copy. As in Chapter 9, type each query of the chapter’s tutorials at the end of the script, under its comment, and run it by selecting it. If you start here, you need only the working copy, made as Step 1 of Tutorial 9.1 describes.

/* Chapter 10: manufacturing variance, joined and summarized
   Database: CharlesRiver_Work.sqlite (a copy of CharlesRiver.sqlite)
   Prepared by: your name, date
   Checks: listed at the end of Tutorial 10.3 */

Step-by-step instructions.

Step 1. Join the closes to their work orders, and test the join. Each close carries the WorkOrderID of the work order it closes, and an inner join that finds every match returns one row for each close.

  1. Type and run the query below.

    -- Tutorial 10.1: the closes joined to their work orders
SELECT woc.WorkOrderCloseID, woc.CloseDate, wo.WorkOrderNumber,
    wo.ItemID, woc.TotalVarianceAmount
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID;

  2. Read the number of rows in the message pane.

  3. Type and run the query below, and compare the two numbers.

    -- Tutorial 10.1: the number of closes, to test the join
SELECT COUNT(*) AS Closes
FROM WorkOrderClose;

The join returns exactly as many rows as the WorkOrderClose table holds, many thousands. Every close therefore matches one work order, and none has been dropped by the inner join.

Step 2. Add the items. The work order’s ItemID leads to the Item table.

  1. Type and run the query below, which adds a second join and the item’s columns.

    -- Tutorial 10.1: the closes with their items
SELECT woc.WorkOrderCloseID, woc.CloseDate, i.ItemCode,
    i.ItemName, i.ItemGroup, woc.TotalVarianceAmount
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID
    INNER JOIN Item AS i ON i.ItemID = wo.ItemID;

  2. Confirm that the number of rows is the same as in Step 1.

Each close now carries the code, name, and group of the item its work order made. The row count is unchanged, so every work order matches an item.

Step 3. Total the variance of fiscal 2026 by item group, and check it against the ledger. A summary is only useful if it agrees with the books.

  1. Type and run the query below.

    -- Tutorial 10.1: the variance of fiscal 2026 by item group
SELECT i.ItemGroup, COUNT(*) AS Closes,
    ROUND(SUM(woc.TotalVarianceAmount), 2) AS TotalVariance
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID
    INNER JOIN Item AS i ON i.ItemID = wo.ItemID
WHERE woc.CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY i.ItemGroup
ORDER BY TotalVariance DESC;

  2. Type and run the query below, which totals the variance postings of fiscal 2026 in the ledger that Tutorial 9.2 isolated.

    -- Tutorial 10.1: the same variance in the ledger
SELECT ROUND(SUM(gl.Debit) - SUM(gl.Credit), 2) AS LedgerVariance
FROM GLEntry AS gl
WHERE gl.AccountID = 93 AND gl.FiscalYear = 2026
    AND gl.SourceDocumentType = 'WorkOrderClose';

  3. Add up the TotalVariance column of item a, and compare it with the ledger total.

Three item groups carry the variance: Furniture, Lighting, and Textiles, the groups whose items Charles River makes in quantity. Furniture holds more than two-thirds of it. The three groups add up to the ledger total to the cent, so the work order records and the ledger agree in total for fiscal 2026.

Step 4. Compare the three years. The year of a close is the first four characters of its CloseDate, and GROUP BY can use the alias CloseYear that the SELECT list gives it.

  1. Type and run the query below.

    -- Tutorial 10.1: the variance by item group and year
SELECT i.ItemGroup, SUBSTR(woc.CloseDate, 1, 4) AS CloseYear,
    COUNT(*) AS Closes,
    ROUND(SUM(woc.TotalVarianceAmount), 2) AS TotalVariance,
    ROUND(SUM(woc.OverheadVarianceAmount), 2) AS OverheadVariance
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID
    INNER JOIN Item AS i ON i.ItemID = wo.ItemID
GROUP BY i.ItemGroup, CloseYear
ORDER BY i.ItemGroup, CloseYear;

  2. For each item group, compare the three years of TotalVariance and of Closes.

The result, shown in Figure 10.3, holds one row for each group and year. Furniture’s variance rose in each year, to nearly twice its fiscal 2024 level, and Lighting’s rose too, while Textiles rose and then fell back in fiscal 2026. In every group, the number of closes fell over the same period, so a larger variance was spread over fewer work orders. The overhead part moves with the total in each group.

A mock of the Execute SQL editor and its result. The editor, on the tab Chapter10.sql, is scrolled to a comment and a query that joins WorkOrderClose to WorkOrder and Item and groups the closes by item group and close year, with the number of closes, the total variance, and the overhead variance. The result holds nine rows, three years each for Furniture, Lighting, and Textiles. The Furniture rows, whose total variance rises in each year while the number of closes falls, are outlined.
Figure 10.3: The Manufacturing Variance by Item Group and Year.

Step 5. Keep the items that carry the most variance. WHERE keeps the closes of fiscal 2026, and HAVING then keeps the items whose total passes a threshold. Group by the item’s key, and include its code, name, and group so that the result is readable.

  1. Type and run the query below.

    -- Tutorial 10.1: items with more than $50,000 of variance in 2026
SELECT i.ItemCode, i.ItemName, i.ItemGroup, COUNT(*) AS Closes,
    ROUND(SUM(woc.TotalVarianceAmount), 2) AS TotalVariance
FROM WorkOrderClose AS woc
    INNER JOIN WorkOrder AS wo ON wo.WorkOrderID = woc.WorkOrderID
    INNER JOIN Item AS i ON i.ItemID = wo.ItemID
WHERE woc.CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY i.ItemID, i.ItemCode, i.ItemName, i.ItemGroup
HAVING SUM(woc.TotalVarianceAmount) > 50000
ORDER BY TotalVariance DESC;

  2. Read the items and their totals, and note the item groups they come from.

  3. Change 50000 to 30000, run the query again, and read the number of rows.

  4. Change the threshold back to 50000.

Only a handful of items pass the threshold of item a, a mix of dining tables, sideboards, and a throw blanket, each with a few dozen closes. At the lower threshold, several times as many pass. No single item stands out, and the largest carries only a small share of the year’s variance. The variance is spread across most of the items Charles River makes. A ranking by total dollars shows where the amounts accumulate, not which items are made inefficiently, but a spread this wide points first to causes that affect production as a whole, alongside the effects of the product mix.

Step 6. List every manufactured item, including those with no closes. A complete list starts from the table that holds the whole population, the items, and left joins the others to it.

  1. Type and run the query below.

    -- Tutorial 10.1: every manufactured item with its closes of 2026
SELECT i.ItemCode, i.ItemName, i.LifecycleStatus,
    COUNT(woc.WorkOrderCloseID) AS Closes2026
FROM Item AS i
    LEFT JOIN WorkOrder AS wo ON wo.ItemID = i.ItemID
    LEFT JOIN WorkOrderClose AS woc ON woc.WorkOrderID = wo.WorkOrderID
        AND woc.CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
WHERE i.SupplyMode = 'Manufactured'
GROUP BY i.ItemID, i.ItemCode, i.ItemName, i.LifecycleStatus
ORDER BY Closes2026, i.ItemCode;

  2. Read the number of rows and the first rows of the result.

  3. Move the date condition from the ON clause to the WHERE clause, by deleting it from the second join and adding AND woc.CloseDate BETWEEN '2026-01-01' AND '2026-12-31' to the WHERE clause, and run the query again.

  4. Compare the number of rows with item b, and then move the condition back into the ON clause.

The query of item a returns one row for every manufactured item, and the first rows show a handful with no closes at all, each counted as zero by COUNT(woc.WorkOrderCloseID). They are discontinued items. With the date condition in WHERE, those items disappear, because their close columns are NULL, like the unmatched row of the left join in Figure 10.1, and a NULL CloseDate is not in any range. The left join has become an inner join, the trap of the section on left joins.

Checkpoint. Check that:

  • the joins in Steps 1 and 2 return as many rows as the WorkOrderClose table holds;
  • the variance of fiscal 2026 by item group adds up to the ledger total of Step 3;
  • Furniture carries most of the variance, and its variance rose in each year while its number of closes fell;
  • HAVING keeps only a handful of items above $50,000, spread across several product types;
  • the complete list of Step 6 keeps the items with no closes only while the date condition is in the ON clause;
  • Chapter10.sql holds the header and the queries of this tutorial, each under a comment.

The variance is not concentrated in a few products. It rises in the groups the plant makes most, while the number of work orders falls, and that pattern turns the question toward the cost of production itself.

10.9 Fan-Out and Double Counting

A join between a header table and its lines returns one row per line, and it repeats the header’s columns on every one of them. That is what you want when you need the header’s date or customer beside each line. It is a trap when you add up an amount that belongs to the header. An invoice with three lines appears three times in a join of SalesInvoice and SalesInvoiceLine, and so does its GrandTotal. Figure 10.4 shows this effect, which is called fan-out, the double counting that Chapter 3 warned about when joining related tables. Summed over fiscal 2026, the join nearly doubles the invoice total. (The lines themselves add up to the invoice’s SubTotal; its GrandTotal also includes the freight and tax recorded on the header.)

A Charles River sales invoice of fiscal 2026 with three lines. Joined to its lines, the invoice's GrandTotal appears on all three rows, so summing GrandTotal over the join counts it three times, while summing the LineTotal of the lines counts each line once. A note states that across fiscal 2026 the sum of GrandTotal over the joined rows is nearly twice the true total of the invoices.
Figure 10.4: Fan-Out: A Header Amount Repeated on Every Line.

The same effect appears whenever a join reaches from one table into another that has several rows for each of its rows. Most work orders have three or four operations, so a query that joins work order closes to their operations, perhaps to see the work centers, repeats each close’s variance three or four times. A sum of the variance over that join is several times the real figure. Counting has the same trap: a count of the rows of a join counts lines, not headers, which is why COUNT(DISTINCT) exists.

The rule is to add up each amount at the level of the table it belongs to. Sum GrandTotal over SalesInvoice alone, and LineTotal over SalesInvoiceLine. When a question needs amounts from both levels, summarize the many side first and then join the result to the one side, so that each row being joined represents one header. The derived tables of the next section show how.

10.10 Subqueries

A subquery is a query written inside another query, in parentheses. It lets you answer in one statement a question that takes two steps.

10.10.1 Scalar Subqueries

A subquery that returns a single value is a scalar subquery, and it can stand wherever a single value can. This query counts the closes of fiscal 2026 whose variance was above the year’s average, which the subquery calculates first:

SELECT COUNT(*) AS AboveAverage
FROM WorkOrderClose
WHERE CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
    AND TotalVarianceAmount > (
        SELECT AVG(TotalVarianceAmount)
        FROM WorkOrderClose
        WHERE CloseDate BETWEEN '2026-01-01' AND '2026-12-31');

A scalar subquery can also stand in the SELECT list, where two of them can put totals from different tables side by side, as Tutorial 10.2 does to tie the closes to the ledger. It should return at most one row. If it finds none, its value is NULL; if it returns several, SQLite quietly uses the first, while most other systems stop with an error.

10.10.2 Subqueries in WHERE

A subquery that returns a column can follow IN. This query totals the closes of fiscal 2026 whose work order made a Furniture item, with the list of those work orders supplied by the subquery:

SELECT COUNT(*) AS FurnitureCloses,
    ROUND(SUM(TotalVarianceAmount), 2) AS Variance
FROM WorkOrderClose
WHERE CloseDate BETWEEN '2026-01-01' AND '2026-12-31'
    AND WorkOrderID IN (
        SELECT wo.WorkOrderID
        FROM WorkOrder AS wo
            INNER JOIN Item AS i ON i.ItemID = wo.ItemID
        WHERE i.ItemGroup = 'Furniture');

The subqueries so far run once, and the outer query uses their result. A correlated subquery refers to a column of the outer query, so it runs again for each outer row. The NOT EXISTS test of the section on left joins is one. This query is another, a scalar subquery in the SELECT list that counts each account’s postings of fiscal 2026:

SELECT a.AccountNumber, a.AccountName,
    (SELECT COUNT(*)
     FROM GLEntry AS gl
     WHERE gl.AccountID = a.AccountID AND gl.FiscalYear = 2026)
        AS Postings2026
FROM Account AS a
ORDER BY Postings2026 DESC;

The subquery runs once for each of the accounts, each time for a different AccountID. It is quick here because GLEntry has an index on AccountID and FiscalYear. A correlated subquery that must search a large table without an index, once for each of thousands of rows, can take minutes. A join to a derived table that summarizes the large table once, or an index, is then often faster, but test the query rather than assume that one form always wins.

10.10.3 Derived Tables

A subquery in the FROM clause produces a table of its own, called a derived table, which the outer query can join and summarize like any other. Give it an alias: SQLite does not insist, but many systems do, and the outer query needs the alias to name its columns. Derived tables are a common way to avoid fan-out, and to combine results calculated at different levels. This query sums the lines of each invoice in a derived table, one row per invoice, and joins the result to the invoice headers:

SELECT COUNT(*) AS Invoices,
    ROUND(SUM(si.SubTotal), 2) AS SubTotals,
    ROUND(SUM(l.LineSum), 2) AS LineSums
FROM SalesInvoice AS si
    INNER JOIN (
        SELECT SalesInvoiceID, SUM(LineTotal) AS LineSum
        FROM SalesInvoiceLine
        GROUP BY SalesInvoiceID
    ) AS l ON l.SalesInvoiceID = si.SalesInvoiceID
WHERE si.InvoiceDate BETWEEN '2026-01-01' AND '2026-12-31';

Each invoice meets exactly one row of the derived table, so nothing is repeated, and the sum of the invoices’ SubTotals equals the sum of their lines. The same pattern combines output summarized by year from one set of tables with hours summarized by year from another, which you will do in Tutorial 10.3.

10.11 The Order in Which a Query Runs

A query is written in one order and evaluated, logically, in another. Figure 10.5 shows both. The database first assembles the rows from the tables in FROM and their joins. It then keeps the rows that meet the WHERE condition, forms the groups, and keeps the groups that meet the HAVING condition. Only then does it calculate the columns of the SELECT list, removing duplicate rows if DISTINCT asks it to. Finally, it sorts the result with ORDER BY and applies LIMIT. The database may take shortcuts that are faster, but the result is always the one this order describes, which is why it is called the logical evaluation order.

Two columns. The written order lists SELECT with DISTINCT, FROM and JOIN, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT, each with a numbered badge giving the step at which it is evaluated. The evaluated order lists the same clauses by step: 1 FROM and JOIN, 2 WHERE, 3 GROUP BY, 4 HAVING, 5 SELECT and DISTINCT, 6 ORDER BY, and 7 LIMIT, with a note beside each step on what it does.
Figure 10.5: The Order in Which a Query Is Written and Evaluated.

The evaluation order explains several rules you have already met. WHERE cannot use an aggregate, because the groups do not yet exist when WHERE runs. HAVING can, because it runs after grouping. ORDER BY can sort by an alias defined in the SELECT list, because it runs after SELECT.

By the same logic, neither WHERE nor GROUP BY should be able to use a column alias, because both run before SELECT defines it. For WHERE, most database systems enforce the rule, and SQLite, which does not, invites a habit to avoid. For GROUP BY, SQLite, PostgreSQL, and MySQL all look up an alias from the SELECT list, and the queries in this book use it, because GROUP BY CloseYear is easier to read than repeating the expression. Microsoft SQL Server does not allow it, so in that system you would repeat the expression, with SQL Server’s own function, SUBSTRING, as in GROUP BY SUBSTRING(woc.CloseDate, 1, 4).

10.12 Guided Tutorial 10.2: Building the Trial Balance and Following the Conversion Cost

Context and objective. Chapter 6 built the pre-closing trial balance of fiscal 2026 in Excel, with Power Query’s Group By, and turned it into the income statement of Figure 6.7. In this tutorial, you will put two totals side by side with scalar subqueries, build the trial balance again with one query, and check with derived tables that its debits equal its credits. You will then use the ledger to follow the cost of production through account 1090 Manufacturing Cost Clearing, the account where the manufacturing variance arises.

Prerequisites. Complete Tutorial 10.1, and continue at the end of Chapter10.sql. If you start here, make the working copy as Step 1 of Tutorial 9.1 describes, and download Chapter10.sql as it stands at the end of Tutorial 10.1 from Companion Files.

Step-by-step instructions.

Step 1. Put the closes and the ledger side by side. In Tutorial 10.1 you compared two totals from two queries. Two scalar subqueries in one SELECT list put them in the same row.

  1. Type and run the query below.

    -- Tutorial 10.2: the closes and the ledger side by side
SELECT
    (SELECT ROUND(SUM(TotalVarianceAmount), 2)
     FROM WorkOrderClose
     WHERE CloseDate BETWEEN '2026-01-01' AND '2026-12-31') AS Closes,
    (SELECT ROUND(SUM(Debit) - SUM(Credit), 2)
     FROM GLEntry
     WHERE AccountID = 93 AND FiscalYear = 2026
         AND SourceDocumentType = 'WorkOrderClose') AS Ledger;

  2. Compare the two columns.

The result is a single row, and the two columns are equal. The outer SELECT has no FROM clause of its own, because each subquery reads its own table. A reconciliation written this way can be saved and rerun at every year-end, and Chapter 12 uses the same pattern to reconcile the populations it tests.

Step 2. Build the pre-closing trial balance. A trial balance groups the ledger rows by account and nets their debits and credits. The pre-closing balance leaves out the two entries that close fiscal 2026.

  1. Type and run the query below.

    -- Tutorial 10.2: the pre-closing trial balance of fiscal 2026
SELECT a.AccountNumber, a.AccountName, a.AccountType,
    ROUND(SUM(gl.Debit) - SUM(gl.Credit), 2) AS Balance
FROM GLEntry AS gl
    INNER JOIN Account AS a ON a.AccountID = gl.AccountID
WHERE gl.PostingDate <= '2026-12-31'
    AND gl.VoucherNumber NOT IN ('JE-2026-000296', 'JE-2026-000297')
GROUP BY a.AccountID, a.AccountNumber, a.AccountName, a.AccountType
ORDER BY a.AccountNumber;

  2. Read the number of rows, and compare the balances of the first accounts with the trial balance in Figure 6.7.

The result, shown in Figure 10.6, has one row for each account with postings, the same accounts as Chapter 6’s trial balance, with the same balances. A positive balance is a debit balance and a negative one a credit balance. As Chapter 6 explained, the revenue and expense accounts hold only fiscal 2026, because the closing entries of the earlier years brought them back to zero.

A mock of the Execute SQL editor and its result. The editor, on the tab Chapter10.sql, is scrolled to a comment and a query that joins GLEntry to Account, keeps the postings through 2026-12-31 without the two closing entries of fiscal 2026, and groups them by account. The first rows of the result show account numbers, names, types, and balances, positive for debit balances and negative for credit balances. The message pane reports the number of accounts returned.
Figure 10.6: The Pre-Closing Trial Balance of Fiscal 2026.

Step 3. Check that debits equal credits. The trial balance becomes a derived table, which an outer query can total.

  1. Type and run the query below, which wraps the grouping of Step 2 in a subquery.

    -- Tutorial 10.2: the net of all the balances
SELECT COUNT(*) AS Accounts, ROUND(SUM(tb.Balance), 2) AS NetBalance
FROM (
    SELECT gl.AccountID,
        ROUND(SUM(gl.Debit) - SUM(gl.Credit), 2) AS Balance
    FROM GLEntry AS gl
    WHERE gl.PostingDate <= '2026-12-31'
        AND gl.VoucherNumber NOT IN ('JE-2026-000296', 'JE-2026-000297')
    GROUP BY gl.AccountID
) AS tb;

  2. Copy the query of item a, with its comment, and paste the copy at the end of the script.

  3. Edit the copy until it reads as below: change the comment and the SELECT list, and add the WHERE clause after the alias of the derived table, so that only the debit balances are totaled. Run it.

    -- Tutorial 10.2: the total of the debit balances
SELECT COUNT(*) AS DebitAccounts,
    ROUND(SUM(tb.Balance), 2) AS DebitBalances
FROM (
    SELECT gl.AccountID,
        ROUND(SUM(gl.Debit) - SUM(gl.Credit), 2) AS Balance
    FROM GLEntry AS gl
    WHERE gl.PostingDate <= '2026-12-31'
        AND gl.VoucherNumber NOT IN ('JE-2026-000296', 'JE-2026-000297')
    GROUP BY gl.AccountID
) AS tb
WHERE tb.Balance > 0;

The net of all the balances is zero, which means that the debit balances equal the credit balances. The total of the debit balances in item c is the control total of Chapter 6’s trial balance. Two tools and two methods give the same trial balance, so the SQL and Excel reconstructions agree on the recorded balances, and the debit balances equal the credit balances. Neither check shows that the entries behind them are complete or correctly classified, as Tutorial 4.1, Step 10 showed for control totals.

Step 4. Follow account 1090 by year and source. Account 1090 collects the actual cost of converting materials into products and releases the standard cost of what is produced.

  1. Type and run the query below.

    -- Tutorial 10.2: account 1090 by year and source
SELECT gl.FiscalYear, gl.SourceDocumentType,
    ROUND(SUM(gl.Debit), 2) AS Debits,
    ROUND(SUM(gl.Credit), 2) AS Credits
FROM GLEntry AS gl
WHERE gl.AccountID = 92 AND gl.FiscalYear BETWEEN 2024 AND 2026
GROUP BY gl.FiscalYear, gl.SourceDocumentType
ORDER BY gl.FiscalYear, gl.SourceDocumentType;

  2. For each year, compare the debits from PayrollSummary and JournalEntry with the credits from ProductionCompletion.

Four kinds of source document post to the account. PayrollSummary debits it with the manufacturing payroll, and journal entries debit it with other costs of the plant. ProductionCompletion credits it with the standard conversion cost of the units completed. WorkOrderClose clears the difference to the variance account. The payroll debits rose steeply from fiscal 2024 to fiscal 2026, while the standard cost released by completions stayed nearly flat.

Step 5. Split the conversion variance into labor and overhead. Each close splits its conversion variance in two, and the close records hold both parts.

  1. Type and run the query below.

    -- Tutorial 10.2: the labor and overhead parts by year
SELECT SUBSTR(CloseDate, 1, 4) AS CloseYear,
    ROUND(SUM(DirectLaborVarianceAmount), 2) AS LaborVariance,
    ROUND(SUM(OverheadVarianceAmount), 2) AS OverheadVariance,
    ROUND(SUM(ConversionVarianceAmount), 2) AS ConversionVariance
FROM WorkOrderClose
GROUP BY CloseYear
ORDER BY CloseYear;

  2. Compare the two parts across the three years, and compare the ConversionVariance of each year with the credits less the debits of WorkOrderClose in Step 4.

The direct labor part compares the direct labor charged to the work orders with its standard. It is negative, that is favorable, in every year, and it shrank to nearly zero by fiscal 2026, which explains part of the variance’s rise. The overhead part holds the rest of the conversion cost, including the time that no work order carries, and it grew in each year, so most of the extra payroll reached the variance as overhead. The two parts add up to the conversion variance, which equals what the closes cleared from account 1090 in each year.

Step 6. Identify the journal entries. GLEntry has no JournalEntryID, but a journal entry row’s SourceDocumentID holds it, and the join names the document type as part of its condition, as the section on following the keys explained.

  1. Type and run the query below.

    -- Tutorial 10.2: the journal entries posted to account 1090
SELECT gl.FiscalYear, je.EntryType,
    ROUND(SUM(gl.Debit), 2) AS Debits
FROM GLEntry AS gl
    INNER JOIN JournalEntry AS je
        ON gl.SourceDocumentType = 'JournalEntry'
        AND je.JournalEntryID = gl.SourceDocumentID
WHERE gl.AccountID = 92
GROUP BY gl.FiscalYear, je.EntryType
ORDER BY gl.FiscalYear, je.EntryType;

  2. Compare the amounts of each entry type across the three years.

The journal entries are of two types, Factory Overhead and the Depreciation of the manufacturing equipment. Factory overhead stayed close to the same amount in each year, and depreciation grew a little. Figure 10.7 puts the pieces together: the flow of fiscal 2026, and the three years side by side. Of the costs that flow into account 1090, the payroll grew by far the most, and the standard cost that production released did not keep pace with it.

A flow diagram of account 1090 Manufacturing Cost Clearing in fiscal 2026. On the left, the manufacturing payroll, factory overhead, and depreciation of manufacturing equipment flow into the account as debits. On the right, the standard conversion cost of the units completed flows out to finished goods inventory, and the conversion variance flows out through work order closes to account 5080 Manufacturing Variance. The payroll is the largest inflow, and the amount sent to the variance account is a large part of the total. A note gives the year's small net change in the account. Below, a table gives the same postings for fiscal 2024, 2025, and 2026, with the variance split into its direct labor and overhead parts: the payroll rises in each year, the standard cost released stays nearly flat, the favorable labor part shrinks, and the highlighted overhead part grows.
Figure 10.7: The Flow of Conversion Cost Through Account 1090.

Checkpoint. Check that:

  • the scalar subqueries of Step 1 return the same total for the closes and the ledger;
  • the trial balance returns the same accounts and balances as Chapter 6’s, and the net of all its balances is zero;
  • account 1090 receives payroll and journal entries and releases the standard cost of completions;
  • the payroll charged to production rose in each year, while the standard cost released stayed nearly flat;
  • the labor part of the variance was favorable and shrank, while the overhead part grew;
  • Chapter10.sql holds this tutorial’s queries under their comments.

The ledger locates the variance precisely. It is the part of the actual conversion cost, mostly payroll, that the standard cost of what the plant produced did not absorb, and it arises mostly in overhead.

10.13 Measuring the Plant Against Its Standards

The controller’s third question is about the plant, and the tables of Table 10.1 let you measure it the way a standard cost system does. A standard cost system compares the inputs actually used with the inputs the standards allow for the output actually achieved (Datar & Rajan, 2021). For direct labor, the input allowed is the standard hours allowed, the units completed times the standard labor hours of each unit. A labor efficiency variance compares them with the direct hours actually used on that output, priced at the standard rate. It is the flexible budget of Chapter 7 applied to hours, because the standard is recalculated at the output actually achieved before it is compared with the actual. Tutorial 10.3 makes a simpler comparison, of the hours recorded in each year with the standard hours of the year’s completed output. It is an observed-hours diagnostic, not a labor efficiency variance, because it does not match each work order’s hours with its own output or allow for work in process.

Not every hour of a plant is direct labor. Indirect labor, the time that no single work order carries, is part of manufacturing overhead, not of direct labor. The Charles River records charge overhead to production at a standard amount for each unit completed, so the cost of more indirect time can raise the overhead part of the variance, not the labor part. The fixed part of overhead is absorbed only by the units made, so output below the volume on which the overhead rate was set leaves some of it unabsorbed, an effect called the production-volume variance (Datar & Rajan, 2021).

The Charles River records keep these kinds of time apart. LaborTimeEntry records each employee’s hours, dated by the day worked, with a LaborType. Direct Manufacturing time is meant to be recorded against a work order operation, and Tutorial 10.3 tests the exceptions. Indirect Manufacturing time is plant time with no operation, and NonManufacturing time belongs to the rest of the company. PayrollRegisterLine records the pay, with regular and overtime earnings and their hours, together with salaries, employer taxes, benefits, and deductions, which have no hours. Figure 10.8 shows these tables and the keys that link them, in the notation of Chapter 3. A payroll period belongs to the fiscal year of its pay date, so the period that ends in late December is paid, and counted, in the next year. Because the labor time entries are dated by the day worked, as the completions are by the day completed, Tutorial 10.3 measures the hours with them and uses the payroll for what the hours cost. It profiles both tables first, to find which days each one covers.

An entity-relationship diagram of the Payroll and Time tables. TimeClockEntry records each employee's day, with clock times and regular and overtime hours, and links to the OvertimeApproval that supports its overtime. LaborTimeEntry splits the day's time by labor type and links to its time clock entry and, for direct labor, to a work order operation from the Manufacturing group. PayrollPeriod groups the PayrollRegister rows; each register has its lines and, once paid, a PayrollPayment, and names its employee and cost center. Employee, CostCenter, and WorkOrderOperation, from other table groups, have gray headers and connect with heavier amber lines.
Figure 10.8: The Payroll and Time Tables.

10.14 Guided Tutorial 10.3: Comparing Output with the Hours It Took

Context and objective. The controller’s third question is whether the variance comes from volume: whether the plant made fewer units over which to spread its costs. In this tutorial, you will measure the plant’s output and the standard hours it required, the hours and pay of the manufacturing payroll, and the people it paid. You will then combine output and recorded hours with derived tables into one measure for each year and kind of labor, and test the time that no work order operation carries.

Prerequisites. Complete Tutorials 10.1 and 10.2, and continue at the end of Chapter10.sql. If you start here, make the working copy as Step 1 of Tutorial 9.1 describes, and download Chapter10.sql as it stands at the end of Tutorial 10.2 from Companion Files. Figure 10.8 shows the payroll and time tables that this tutorial joins.

Step-by-step instructions.

Step 1. Measure the output and its standard hours. A production completion line records the quantity completed, and the item records the standard labor hours each unit needs, so the standard hours are an aggregate of a calculation.

  1. Type and run the query below.

    -- Tutorial 10.3: the output and its standard hours by year
SELECT SUBSTR(pc.CompletionDate, 1, 4) AS CompletionYear,
    ROUND(SUM(pcl.QuantityCompleted), 0) AS UnitsCompleted,
    ROUND(SUM(pcl.QuantityCompleted
        * i.StandardLaborHoursPerUnit), 0) AS StandardHours
FROM ProductionCompletionLine AS pcl
    INNER JOIN ProductionCompletion AS pc
        ON pc.ProductionCompletionID = pcl.ProductionCompletionID
    INNER JOIN Item AS i ON i.ItemID = pcl.ItemID
GROUP BY CompletionYear
ORDER BY CompletionYear;

  2. Compare the three years of UnitsCompleted and StandardHours.

Output was nearly flat. The plant completed about the same number of units in fiscal 2024 and fiscal 2026, with a dip of about six percent in fiscal 2025, and the standard labor hours those units required moved with them. The item table keeps one standard for each item and no history of it, so the query applies the same standard to all three years. The completion lines of every year carry the item’s current standard labor cost, so the standards appear to have held over the period.

Step 2. Measure the hours and pay of the manufacturing payroll. The payroll register records each employee’s pay for a period, and its lines record the hours of regular and overtime earnings.

  1. Type and run the query below.

    -- Tutorial 10.3: the hours and pay of the manufacturing payroll
SELECT pp.FiscalYear, prl.LineType,
    ROUND(SUM(prl.Hours), 0) AS Hours,
    ROUND(SUM(prl.Amount), 2) AS Amount
FROM PayrollRegisterLine AS prl
    INNER JOIN PayrollRegister AS pr
        ON pr.PayrollRegisterID = prl.PayrollRegisterID
    INNER JOIN PayrollPeriod AS pp
        ON pp.PayrollPeriodID = pr.PayrollPeriodID
    INNER JOIN CostCenter AS cc ON cc.CostCenterID = pr.CostCenterID
WHERE cc.CostCenterName = 'Manufacturing'
    AND prl.LineType IN ('Regular Earnings', 'Overtime Earnings')
    AND pp.FiscalYear BETWEEN 2024 AND 2026
GROUP BY pp.FiscalYear, prl.LineType
ORDER BY pp.FiscalYear, prl.LineType;

  2. Compare the hours and amounts of each line type across the three years.

The regular hours paid to the manufacturing cost center rose in each year, and the overtime hours more than doubled from fiscal 2024 to fiscal 2026. The amounts rose with them, and overtime, paid at a premium, rose fastest. The years here are payroll years, which follow the pay date, as the section on measuring the plant explained, and fiscal 2024 holds one pay period fewer than the two years after it.

Step 3. Count the people. More hours could mean more people or more hours per person, and a count at the wrong level would mislead.

  1. Type and run the query below.

    -- Tutorial 10.3: the registers and the people they paid
SELECT pp.FiscalYear, COUNT(*) AS Registers,
    COUNT(DISTINCT pr.EmployeeID) AS Employees
FROM PayrollRegister AS pr
    INNER JOIN PayrollPeriod AS pp
        ON pp.PayrollPeriodID = pr.PayrollPeriodID
    INNER JOIN CostCenter AS cc ON cc.CostCenterID = pr.CostCenterID
WHERE cc.CostCenterName = 'Manufacturing'
    AND pp.FiscalYear BETWEEN 2024 AND 2026
GROUP BY pp.FiscalYear;

  2. Compare the Registers and Employees columns.

COUNT(*) counts the registers, one for each employee and pay period, well over a thousand a year, while COUNT(DISTINCT pr.EmployeeID) counts the different people paid in the year. The manufacturing payroll paid the same number of people in each year, so the extra hours did not come from a larger workforce, and more of them were paid as overtime.

Step 4. Profile the two tables before comparing them. A ratio compares like with like only if its two sides cover the same period. A profile of each table, with the tests of Table 10.3, shows what it holds and which days it covers.

  1. Type and run the query below.

    -- Tutorial 10.3: a profile of the labor time entries
SELECT COUNT(*) AS Entries,
    COUNT(WorkOrderOperationID) AS WithOperation,
    COUNT(DISTINCT EmployeeID) AS Employees,
    MIN(WorkDate) AS FirstWorkDate, MAX(WorkDate) AS LastWorkDate
FROM LaborTimeEntry;

  2. Type and run the query below.

    -- Tutorial 10.3: a profile of the production completions
SELECT COUNT(*) AS Completions,
    MIN(CompletionDate) AS FirstCompletionDate,
    MAX(CompletionDate) AS LastCompletionDate
FROM ProductionCompletion;

  3. Compare the first and last dates of the two tables.

About three-quarters of the labor time entries carry a work order operation. The rest are the indirect and non-manufacturing time of the section on measuring the plant, which Step 7 tests. Both tables begin in January 2024, and the completions run to the last day of fiscal 2026, but the time records end on December 11, 2026. Hours counted to the end of the year would miss the last three weeks of December, while the output would not, and the ratio of the two would look better than it is. The kinds of time do not all end together, either. Exercise 10.5 profiles them by LaborType and finds that indirect time ends a week earlier, on December 4, 2026, so its 2026 ratio covers a little less of the year than the output it is compared with. A first and a last date show where a table’s records begin and end, not that every day between them is complete. This is the timeliness test of Table 2.3: the date range of each extract, and a partial last period.

Step 5. Find why the time records end early. The pay periods show which time has been processed.

  1. Type and run the query below, which lists the pay periods at the end of the data.

    -- Tutorial 10.3: the pay periods at the end of the data
SELECT PeriodNumber, PeriodStartDate, PeriodEndDate, PayDate, Status
FROM PayrollPeriod
WHERE PeriodEndDate >= '2026-12-01'
ORDER BY PeriodStartDate;

  2. Read the Status of each pay period.

The time records end on the last working day of the last pay period that was processed. The two periods after it are still Open, and no time has been recorded for them. The next step therefore counts only the output completed up to the last day with time records.

Step 6. Compare the hours recorded with the standard hours, by kind of labor. The hours come from the labor time entries, dated by the day worked, and the output from the completions up to the last day with time records, which a scalar subquery finds. Each is summarized by year, unrounded, in a derived table, and the outer query joins the two on the year and rounds only what it shows.

  1. Type and run the query below.

    -- Tutorial 10.3: hours per standard hour, by labor type
SELECT h.WorkYear, h.LaborType, ROUND(h.Hours, 0) AS Hours,
    ROUND(o.StandardHours, 0) AS StandardHours,
    ROUND(h.Hours / o.StandardHours, 2) AS HoursPerStandardHour
FROM (
    SELECT SUBSTR(lt.WorkDate, 1, 4) AS WorkYear, lt.LaborType,
        SUM(lt.RegularHours + lt.OvertimeHours) AS Hours
    FROM LaborTimeEntry AS lt
    WHERE lt.LaborType <> 'NonManufacturing'
    GROUP BY WorkYear, lt.LaborType
) AS h
    INNER JOIN (
        SELECT SUBSTR(pc.CompletionDate, 1, 4) AS WorkYear,
            SUM(pcl.QuantityCompleted
                * i.StandardLaborHoursPerUnit) AS StandardHours
        FROM ProductionCompletionLine AS pcl
            INNER JOIN ProductionCompletion AS pc
                ON pc.ProductionCompletionID = pcl.ProductionCompletionID
            INNER JOIN Item AS i ON i.ItemID = pcl.ItemID
        WHERE pc.CompletionDate
            <= (SELECT MAX(WorkDate) FROM LaborTimeEntry)
        GROUP BY WorkYear
    ) AS o ON o.WorkYear = h.WorkYear
ORDER BY h.WorkYear, h.LaborType;

  2. Read the HoursPerStandardHour column for Direct Manufacturing across the three years.

  3. Read it for Indirect Manufacturing, and add the two rows of each year.

The result, shown in Figure 10.9, has two rows for each year. Both derived tables return the year as text, so the join compares text with text. Counting the output only to the last day with time records changes only fiscal 2026, the one year whose time records stop early. The Direct Manufacturing hours recorded stayed below the standard hours in every year, although they moved closer to them, and a few dozen of those hours carry no valid operation, as Step 7 shows. Indirect time, which no work order carries, grew from about a third of the standard hours to more than half. The two rows of a year share the same standard hours, so their sum is, to within rounding, the plant’s total hours per standard hour. Together, by fiscal 2026 the plant recorded about half as many hours again as the standard hours of its output, and most of the growth was indirect. Read the ratios as an observed-records diagnostic. Direct and non-manufacturing time end on December 11, 2026, but indirect time on December 4, 2026, so the 2026 indirect row compares a week less of time with the same output. Counted to that earlier day on both sides, hours and output alike, the 2026 direct ratio is somewhat lower and the indirect ratio a little higher, and the pattern of the three years is the same.

A mock of the Execute SQL editor and its result. The editor, on the tab Chapter10.sql, is scrolled to a comment and a query that joins two derived tables on the year: one with the hours of labor time by year and labor type, without NonManufacturing time, and one with the standard labor hours of the units completed. The result has six rows, Direct Manufacturing and Indirect Manufacturing for 2024, 2025, and 2026. The HoursPerStandardHour column, outlined, stays below one for direct labor in every year and rises for indirect labor.
Figure 10.9: Hours per Standard Hour by Labor Type and Year.

Step 7. Test the time that has no work order operation. An anti-join from LaborTimeEntry to WorkOrderOperation finds the time with no operation, and grouping it by LaborType shows which of it is expected.

  1. Type and run the query below.

    -- Tutorial 10.3: labor time with no work order operation
SELECT lt.LaborType, COUNT(*) AS Entries,
    ROUND(SUM(lt.RegularHours + lt.OvertimeHours), 0) AS Hours
FROM LaborTimeEntry AS lt
    LEFT JOIN WorkOrderOperation AS op
        ON op.WorkOrderOperationID = lt.WorkOrderOperationID
WHERE op.WorkOrderOperationID IS NULL
GROUP BY lt.LaborType
ORDER BY lt.LaborType;

  2. Read the three rows of the result.

  3. Add AND lt.WorkOrderOperationID IS NOT NULL to the WHERE clause, run the query again, and read the result.

  4. Remove the condition you added.

Indirect and non-manufacturing time has no operation by design, and it makes up nearly all of the result. The few direct labor entries are exceptions: time charged as direct labor that no operation carries. The query of item c keeps only the entries whose WorkOrderOperationID is filled in but matches no operation, and most of the direct entries are of that kind, keys that point to nothing. They are the untested keys that the chapter warned about, and Exercise 10.5 traces them to their work orders.

Step 8. Complete and save the script. The header comment should record the checks that the chapter’s queries passed.

  1. Replace the last line of the header comment of Chapter10.sql, so that the header reads as below.

    /* Chapter 10: manufacturing variance, joined and summarized
   Database: CharlesRiver_Work.sqlite (a copy of CharlesRiver.sqlite)
   Prepared by: your name, date
   Checks: 2026 closes equal the ledger; the trial balance nets to zero */

  2. Click Save SQL file.

The script now holds every query of the chapter’s tutorials under a comment that says what it answers, and a header that records the checks the results passed.

Checkpoint. Check that:

  • output was nearly flat, with a dip in fiscal 2025, and its standard hours moved with it;
  • the payroll paid the same number of people in each year, with overtime hours more than doubling;
  • COUNT(*) counts registers and COUNT(DISTINCT pr.EmployeeID) counts people;
  • the profiles show that the time records end on December 11, 2026, so the output is counted only up to that day, although indirect time ends a week earlier;
  • the direct hours recorded stayed below the standard hours in every year, while indirect time grew;
  • a few direct labor entries have no valid operation;
  • Chapter10.sql is saved with a completed header and a comment above each query.

The records point away from volume. The plant produced about as much as before, and the direct hours it recorded stayed below the standard hours of its output, but it recorded many more hours of indirect time and paid for more overtime. Indirect labor is overhead, so the extra hours are consistent with the growth that Tutorial 10.2 found in the variance’s overhead part. The ratios are an observed-hours diagnostic, not a labor efficiency variance or a measure of the production-volume variance, and these queries do not measure how much of that growth each cause explains.

10.15 Looking Ahead

This chapter has taught you to combine tables and summarize their rows. You joined tables with INNER JOIN on their logical keys and tested each join by its keys and its row count. You kept unmatched rows with LEFT JOIN, listed whole populations, and found missing records with anti-joins and NOT EXISTS. You summarized with aggregate functions, grouped by key, filtered groups with HAVING, and avoided the double counting of fan-out. You wrote subqueries in SELECT, WHERE, and FROM, including correlated ones, and you learned the logical order in which a query is evaluated. Along the way, you tied the variance to the ledger, rebuilt the pre-closing trial balance of fiscal 2026, and followed the conversion cost of production through the ledger, after profiling the tables you relied on. Where Chapter 9’s queries described the records, most of this chapter’s were diagnostic analytics, in the terms of Table 1.1. They narrowed the search for why the variance grew, by breaking it down by product, year, and part, and by comparing the hours recorded with the standard hours of the output.

The queries narrow the controller’s volume question. The plant completed about as many units in fiscal 2026 as in fiscal 2024, with a dip in fiscal 2025, and those units needed about as many standard labor hours, so the records point to the cost of production more than to volume. The direct labor recorded stayed below those standard hours in every year. The extra hours were elsewhere. The indirect time recorded, which no work order carries, grew by about two-thirds, and the overtime paid more than doubled. Indirect labor is overhead, so its growth is consistent with the growth of the variance’s overhead part, while the favorable labor part shrank as direct hours moved toward the standard and more of them were paid as overtime. These comparisons are an observed-hours diagnostic, not a labor efficiency variance or a measure of the production-volume variance, and they do not yet show how much of the change each cause explains. What the controller needs next is a measure she can follow month by month, one that shows whether the gap between the hours recorded and the standard hours is still widening. Building that measure requires classifying rows, calculating across months, and organizing a long query into steps, which is the work of Chapter 11.

10.16 Chapter Summary

A relational database stores each fact once and links its tables through keys, so most accounting questions require joining tables. An inner join returns the pairs of rows whose keys match. Columns are qualified with their table or alias, which is required when a name appears in both tables. Because the Charles River database declares no foreign keys, every join is tested by checking that the key on the other side is unique, looking for unmatched keys, and comparing the rows and totals before and after the join. A join to a source document names both the document type and its ID, because each document table numbers its own rows.

A left join keeps every row of the first table, with NULLs where the second table has no match. It lists a whole population, and, written as an anti-join, it finds the records that have no match at all. NOT EXISTS asks the same question, while NOT IN fails silently when its list contains a NULL. A condition on the second table that a NULL cannot meet belongs in the ON clause, because in WHERE it turns the left join into an inner join.

Aggregate functions calculate one value from many rows, and the ones this chapter uses skip NULLs, except COUNT(*). GROUP BY calculates them once per group, and in this book every column of the SELECT list is grouped or aggregated, a rule SQLite does not enforce. Grouping by the key rather than the name keeps records that share a name apart. HAVING filters groups after aggregation, while WHERE filters rows before it. A data profile, a few aggregate queries run before a table is used, applies Chapter 2’s quality tests in SQL: counts of rows and values, unique keys, the values of code columns, and the dates covered. A join from a header to its lines repeats the header’s amounts on every line, so each amount is summed at the level of the table it belongs to.

Subqueries answer questions in two steps. A scalar subquery supplies one value, in the SELECT list or in a condition. A subquery after IN supplies a list, and a correlated subquery runs again for each row of the outer query. In FROM, a subquery forms a derived table, which this chapter uses to summarize each population before the summaries are joined. A query is evaluated, logically, in the order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, which explains where each kind of condition belongs.

Applied to the controller’s questions, the queries tied the manufacturing variance of fiscal 2026 to the ledger to the cent. Furniture carries most of it, and it rose in Furniture and Lighting from year to year. The same joins rebuilt the pre-closing trial balance of fiscal 2026 and followed the conversion cost through account 1090 Manufacturing Cost Clearing, where the payroll charged to production rose while the standard cost released by completions stayed nearly flat. Output was nearly flat, so the records point to the cost of production more than to volume. The direct labor recorded stayed below the standard hours, while indirect time grew by about two-thirds and overtime more than doubled. Indirect labor is overhead, and the growth of the variance appears mostly in its overhead part, consistent with the extra indirect time. The hours comparison is an observed-hours diagnostic, not a labor efficiency variance or a measure of the production-volume variance, and indirect time ends a week before the other time records.

10.17 Key Terms

Aggregate function. A function that calculates one value from many rows, such as COUNT, SUM, AVG, MIN, or MAX. Except for COUNT(*), the aggregate functions of this chapter skip NULLs.

Anti-join. A query that finds the rows of one table with no match in another, written as a left outer join that keeps only the rows where a column of the second table is NULL. The column must be one that is never NULL in a matched row, such as the second table’s key. It is the standard test for missing records, such as ledger rows that point to no document.

Correlated subquery. A subquery that refers to a column of the outer query, so that it is evaluated again for each row of the outer query, as in a NOT EXISTS test or a count of each account’s postings.

Data profile. A short set of queries that describes a table before it is used: the number of rows, how many fill in each column that matters, whether the keys are unique, the values of the code columns, and the dates covered. A profile applies the data quality tests of Chapter 2 to one table and records what can be relied on.

Derived table. A subquery in the FROM clause, which produces a table that the outer query can join, filter, and summarize. It should have an alias, which most systems require and which the outer query needs to name its columns.

Fan-out. The repetition of a row’s values when it is joined to several rows of another table, such as an invoice’s total repeated on each of its lines. Summing a repeated amount counts it more than once.

GROUP BY clause. The clause that divides rows into groups sharing the same values in the named columns and calculates each aggregate once per group. In this book, every other column in the SELECT list is aggregated. It is the SQL counterpart of Power Query’s Group By in Chapter 6.

HAVING. The clause that filters groups after the aggregates are calculated, so that its condition can use them, such as keeping only the accounts whose balance is not zero.

Inner join. A join that returns one row for each pair of rows, one from each table, whose key values match. Rows without a match in the other table do not appear. JOIN written alone means an inner join.

Join. An operation that combines the rows of two tables that meet a matching condition. The joins of this chapter match equal key values to follow a relationship between the tables, the SQL counterpart of a merge in Power Query.

Left outer join. A join kind that keeps every row of the first table and adds the matching rows of the second, leaving the added columns empty where no match exists. In SQL it is written LEFT JOIN, and the added columns hold NULL where the second table has no match.

Logical evaluation order. The order in which a database evaluates the clauses of a query: FROM and its joins, WHERE, GROUP BY, HAVING, SELECT and DISTINCT, ORDER BY, and LIMIT. It differs from the order in which the clauses are written.

NOT EXISTS. A condition that is true when its subquery returns no rows, used to find the rows of one table that have no match in another. Unlike NOT IN, it is not defeated by a NULL in the other table.

Production-volume variance. The part of an overhead variance that arises because output differs from the volume on which the fixed overhead rate was set. When output falls below that volume, some fixed overhead is left unabsorbed.

Scalar subquery. A subquery that returns a single value, which can stand wherever a value can, in the SELECT list or in a condition. When it finds no row, its value is NULL.

Standard hours allowed. The labor hours that the standards allow for the output actually produced, calculated as the units completed times the standard hours per unit. The direct labor efficiency variance compares them with the direct labor hours actually used, priced at the standard rate.

Subquery. A query written inside another query, in parentheses. In SELECT or WHERE, a subquery supplies a value or a list for a condition; in FROM, it forms a derived table.

Table alias. A short name given to a table in the FROM clause with AS, such as WorkOrderClose AS woc, used to qualify its columns. An alias is required when the same table appears twice in one query.

Trial balance. A listing of all general ledger account balances at a specific point in time, used to verify that total debits equal total credits and as the starting point for preparing financial statements. In SQL, it is the ledger rows grouped by account, with their debits and credits netted.

10.18 Multiple Choice Questions

1. An analyst joins WorkOrderClose to WorkOrder with an inner join on WorkOrderID. The result has fewer rows than the WorkOrderClose table. What does this indicate?

  1. The join has combined several closes of the same work order into one row
  2. WorkOrder has more rows than WorkOrderClose, so the join removed some
  3. Some closes have a WorkOrderID that matches no work order
  4. The query needs a GROUP BY clause before it can return every close

2. A query joins WorkOrderClose and WorkOrder and asks for WorkOrderID without naming a table. It stops with the message ambiguous column name: WorkOrderID. What is the best fix?

  1. Qualify the column with its table’s alias, as in woc.WorkOrderID
  2. Add DISTINCT to the SELECT list, so that WorkOrderID appears only once
  3. Change the inner join to a left join, so that one table supplies WorkOrderID
  4. Give the column an alias in the SELECT list, such as WorkOrderID AS ID

3. Which query returns the total variance of each item group, one row per group?

  1. SELECT ItemGroup, TotalVarianceAmount FROM ... ORDER BY ItemGroup
  2. SELECT SUM(TotalVarianceAmount) FROM ... WHERE ItemGroup IS NOT NULL
  3. SELECT ItemGroup, COUNT(ItemGroup) FROM ... GROUP BY TotalVarianceAmount
  4. SELECT ItemGroup, SUM(TotalVarianceAmount) FROM ... GROUP BY ItemGroup

4. Every manufactured item is left joined to WorkOrder and grouped by item. For an item with no work orders, what do COUNT(*) and COUNT(wo.WorkOrderID) return?

  1. 0 and 0
  2. 1 and 0
  3. 1 and 1
  4. NULL and 0

5. An analyst wants the ledger accounts whose balance, the sum of debits minus credits, is not zero. Where does the condition on the balance belong?

  1. In the WHERE clause, because the condition filters the ledger’s rows
  2. In a HAVING clause, because it tests an aggregate of each group
  3. In the ON clause of the join from GLEntry to the Account table
  4. In the ORDER BY clause, after the balance of each account is calculated

6. An auditor runs Item AS i LEFT JOIN WorkOrder AS wo ON wo.ItemID = i.ItemID and keeps only the rows where wo.WorkOrderID IS NULL. What does the query find?

  1. The work orders whose ItemID matches no item
  2. The items that have exactly one work order
  3. Every item, with the number of its work orders
  4. The items for which no work order exists

7. An analyst totals orders with GROUP BY CustomerName. Two different customers are both called Brown LLC. What happens?

  1. Their orders are combined into one row, with nothing in the result to show it
  2. The query fails, because the name is not unique
  3. Each Brown LLC appears on its own row, because SQL tracks the key automatically
  4. The duplicate customer is left out of the result

8. A query joins SalesInvoice to SalesInvoiceLine and sums SalesInvoice.GrandTotal. The result is nearly twice the invoices’ total. Why?

  1. GrandTotal includes sales tax and freight, which the invoice lines do not
  2. The join also matched lines of other invoices that share a line number
  3. Each GrandTotal repeats on every line of its invoice
  4. An inner join returns each matched row twice, once for each of the tables

9. Which query counts the fiscal 2026 closes whose variance was above the average variance of fiscal 2026?

  1. One that uses WHERE TotalVarianceAmount > AVG(TotalVarianceAmount)
  2. One that uses HAVING TotalVarianceAmount > 0 after GROUP BY CloseDate
  3. One that sorts the closes by variance and keeps the first half with LIMIT
  4. One that compares each close with a subquery for the year’s average

10. A query keeps the employees whose EmployeeID is NOT IN the list of requisition approvers. It returns no rows, although most employees have never approved a requisition. What is the most likely cause?

  1. The subquery returns too many rows for NOT IN to compare them all
  2. The list contains a NULL, and no comparison with NULL is true
  3. NOT IN works only with text values, not with integer keys
  4. The subquery must be joined to Employee before NOT IN can use it

11. A query lists every manufactured item with its closed work orders, using Item LEFT JOIN WorkOrder, and adds WHERE wo.Status = 'Closed'. The items with no work orders disappear from the result. Why?

  1. Their Status is NULL, so WHERE removes them, as an inner join would
  2. The ON clause must also name every column that the WHERE clause uses
  3. WHERE runs before the join, so the items are removed before matching
  4. A left join keeps only the rows of the second table that have a match

12. SQLite runs a query that selects ItemName and SUM(TotalVarianceAmount) but groups only by ItemGroup. What is true of the ItemName column in the result?

  1. It lists every item name of the group, separated by commas
  2. It is NULL in every row, because ItemName is not grouped
  3. It shows one row’s name from each group, which means little
  4. It shows the name of the item with the largest variance in each group

13. A trial balance query is wrapped in a derived table, and an outer query sums the Balance column of all the accounts. The result is zero. What does this show?

  1. The debit balances equal the credit balances
  2. The trial balance includes only the balance sheet accounts
  3. The closing entries were included in the query by mistake
  4. Each account’s debits equal that account’s own credits

14. In Tutorial 10.3, the hours recorded and the output are summarized in two derived tables and then joined on the year. Why are they not joined row by row first?

  1. A derived table is required whenever a query joins more than two tables
  2. The labor time and production tables share no column to join on
  3. A derived table runs faster than a join of the underlying tables
  4. Each is recorded at its own level, so both are summarized first

15. From fiscal 2024 to fiscal 2026, Charles River’s output and its standard hours were nearly flat. The direct labor hours recorded stayed below the standard hours, while indirect hours grew by about two-thirds. Where does the cost of the extra indirect hours appear?

  1. In the direct labor part, as an unfavorable labor efficiency variance
  2. In the material part, because more material was used for each unit
  3. In the overhead part, because indirect labor is part of overhead
  4. Nowhere, because the standards for direct labor were met in every year

10.19 Applied Exercises

Work on these exercises in CharlesRiver_Work.sqlite, and save the queries of each exercise in a script of its own, named after the exercise (such as Ex 10.1.sql), with a header comment and a comment above each query. Test every join by its keys, its row count, and its totals, as the chapter does, and group by key rather than by name.

10.19.1 Financial Accounting Exercises

Exercise 10.1: Reconciling Invoice Lines to the Ledger in SQL

Table group: Order-to-Cash, Accounting Core

Scenario. In Chapter 5 you built the InvoiceLines query in Power Query, and in Chapter 6 you tied its fiscal 2026 revenue to the ledger. The controller asks you to repeat the reconciliation with SQL, so that it can be rerun at every year-end without rebuilding a workbook.

Requirements.

  1. Join SalesInvoiceLine to SalesInvoice, Item, and Customer, and confirm that the join returns as many rows as SalesInvoiceLine holds.

  2. Total the LineTotal of the invoices dated in fiscal 2026 by item group, and compare the total with the control total you used in Chapter 6.

  3. Join GLEntry to Account, and total the credits less debits posted by SalesInvoice documents in fiscal 2026 to each revenue account, 4010 to 4040 and 4080.

  4. Explain the difference between requirements (2) and (3). Find the invoices responsible for it by joining the fiscal 2026 SalesInvoice postings to SalesInvoice and keeping the invoices dated before 2026-01-01.

  5. Explain why account 4050 Freight Revenue is not part of the reconciliation.

Deliverable. A script with the reconciliation queries and a short explanation of the difference.

Exercise 10.2: Tracing a Sale from the Ledger to Its Documents

Table group: Accounting Core, Order-to-Cash

Scenario. In Tutorial 3.2 you traced invoice SI-2025-007947 by filtering worksheets one at a time. The audit senior asks you to repeat the trace with joins, and then to extend it from one sale to every revenue posting of a year.

Requirements.

  1. List the ledger rows posted by SI-2025-007947, joining GLEntry to SalesInvoice on the source document and to Account for the account numbers and names. Explain what each row records and show that the debits equal the credits.

  2. Join the invoice to its customer and its lines to their items, and identify the line whose SalesInvoiceLineID equals the SourceLineID of the revenue posting.

  3. For every SalesInvoice posting of fiscal 2026 that carries a SourceLineID, use an anti-join to find the postings whose SourceLineID matches no invoice line. Report the number of postings tested and the number of exceptions.

  4. Write a short memo (one half page) explaining how the source-document columns let an auditor move from the ledger to the documents and back, and what requirement (3) shows and does not show.

Deliverable. A script with the trace queries and a half-page memo.

10.19.2 Managerial Accounting Exercises

Exercise 10.3: Average Order Value by Customer

Table group: Order-to-Cash, Master Data

Scenario. The sales manager wants to know which customers place the largest orders and whether order size differs by customer segment. The controller asks you to prepare the analysis from the sales orders.

Requirements.

  1. Join SalesOrder to Customer and calculate, for each customer, the number of orders, the total OrderTotal, and the average order value, grouped by CustomerID. Sort by average order value from the largest down.

  2. Repeat requirement (1) grouped by CustomerName instead of CustomerID. Compare the number of rows, and find the customer names that appear in more than one row of requirement (1).

  3. Use HAVING to keep only the customers with at least 20 orders, and list the ten with the highest average order value.

  4. Calculate the number of orders and the average order value by CustomerSegment.

  5. Write two or three sentences for the sales manager on how order size differs by segment, and explain why the grouping in requirement (1) is the right one.

Deliverable. A script with the queries and a short note for the sales manager.

Exercise 10.4: Purchase Price Variance by Supplier

Table group: Procure-to-Pay, Accounting Core

Scenario. Exercise 9.2 showed that account 5060 records the difference between supplier invoices and the purchase-order prices at which goods were received, together with purchase tax that cannot be recovered. The purchasing manager asks which suppliers bill above the order price, and whether the account grew because prices rose or because Charles River bought more.

Requirements.

  1. Join the fiscal 2026 postings to account 5060 whose description records a purchase variance to PurchaseInvoice, through the source document, and to Supplier. Total the variance by supplier and list the five largest.

  2. Recalculate the variance from the documents: join PurchaseInvoiceLine to PurchaseInvoice and PurchaseOrderLine, and total the quantity times the difference between the invoice unit cost and the order unit cost, for the invoices dated in fiscal 2026. Compare the total with the ledger’s.

  3. Count the fiscal 2026 invoice lines billed above, below, and at the order price, with one query for each condition.

  4. Calculate, for each year from 2024 to 2026, the total amount invoiced against purchase orders and the price variance, and express the variance as a percentage of the amount invoiced.

  5. Write a short memo (one half page) to the purchasing manager answering both questions, and explain what a comparison of invoice prices with order prices cannot show.

Deliverable. A script with the queries and a half-page memo.

10.19.3 Auditing Exercises

Exercise 10.5: Testing Referential Integrity with Anti-Joins

Table group: Accounting Core, Order-to-Cash, Manufacturing, Payroll and Time

Scenario. Chapter 3 explained that the Charles River database declares no foreign keys, so its relationships must be tested, and it mentioned ledger rows that point to payroll payments that do not exist. The audit senior asks you to run a set of integrity tests with anti-joins before the team relies on the joins in its other work.

Requirements.

  1. Before the tests, profile LaborTimeEntry with the tests of Table 10.3: by LaborType, count the entries, the entries with a WorkOrderOperationID, and the employees, and find the first and last WorkDate. State which results you expected and which you did not.

  2. Find the GLEntry rows whose SourceDocumentType is PayrollPayment and whose SourceDocumentID matches no PayrollPayment. List them with their posting date, amounts, and description, and total their debits and credits.

  3. Find the customers with no sales order in the database, and check their IsActive flag.

  4. Find the work orders that have no work order operations, and report their status.

  5. Find the labor time entries whose WorkOrderOperationID is filled in but matches no work order operation, and identify their work orders and hours.

  6. For each finding, state whether it is an integrity failure, a process exception, or an expected condition, and recommend a follow-up.

Deliverable. A script with the profile and the anti-join tests, and a table of findings with dispositions.

Exercise 10.6: Suppliers That Share an Employee’s Address

Table group: Procure-to-Pay, Master Data

Scenario. In Exercise 5.5 you matched supplier addresses against employee addresses in Excel and found suppliers that may be related parties. The audit senior asks you to repeat the test with a join, to measure how much Charles River paid those suppliers, and to add a test for customers that share a name.

Requirements.

  1. Join Supplier to Employee on Address, and list each matching supplier with the employee, the employee’s job title, and the supplier’s IsApproved flag.

  2. For each matching supplier, total the number and amount of supplier invoices and of payments dated through 2026-12-31. Group by SupplierID, and total the invoices and the payments separately, in two queries or two derived tables, so that a join of one to the other does not repeat either.

  3. Use GROUP BY and HAVING to find the customer names shared by more than one CustomerID, and list those customers with their segments.

  4. Explain why the join in requirement (1) is a stronger test than a visual comparison of the two lists, and what it cannot tell you.

  5. Write a short memo (one half page) to the audit senior with the findings and the next procedures you recommend.

Deliverable. A script with the queries and a half-page memo.

10.20 Further Reading

Borthick, A. F., Schneider, G. P., & O’Brien, A. D. (2024). Developing business process and query skills for solving business problems. Issues in Accounting Education, 39(3), 99–108. https://doi.org/10.2308/issues-2023-053 In this case, learners follow a business process diagram for the sales and returns of a car dealer and its manufacturer, and then query a database to find out why the two parties’ dashboards disagree. It practices the operations of this chapter, joining tables, aggregating data, and verifying that a query is correct, and it can be worked in any SQL system.

Cooper, R., & Kaplan, R. S. (1992). Activity-based systems: Measuring the costs of resource usage. Accounting Horizons, 6(3), 1–13. https://doi.org/10.2308/ah-9604083361 The authors set out the conceptual basis of activity-based cost systems, which separate the cost of the resources supplied from the cost of the resources that activities use, and they explain how measuring unused capacity differs from a traditional production-volume variance. The distinction helps frame questions about capacity, although the chapter’s ratio of indirect hours does not measure unused capacity.

Datar, S. M., & Rajan, M. V. (2021). Horngren’s cost accounting: A managerial emphasis (17th ed.). Pearson. https://www.pearson.com/en-us/pearsonplus/p/9780136713845 A standard cost accounting text. Its chapters on flexible budgets and on direct-cost and overhead variances explain how standard costs are set and how actual inputs are compared with the inputs allowed for the output achieved, the framework behind the manufacturing variance that this Part investigates.

Farkas, M., Kersting, L., & Stephens, W. (2016). Modern Watch Company: An instructional resource for presenting and learning actual, normal, and standard costing systems, and variable and fixed overhead variance analysis. Journal of Accounting Education, 35, 56–68. https://doi.org/10.1016/j.jaccedu.2016.02.001 This instructional resource takes students through actual, normal, and standard costing on one set of data, with particular attention to overhead variances and the fixed overhead production volume variance. It is a useful companion to the controller’s question of whether a variance comes from volume or from cost.

Li, J., & Lee, L. (2021). Teaching data joins: A conceptual approach using SQL, Alteryx, and Tableau. AIS Educator Journal, 16(1), 60–104. https://doi.org/10.3194/1935-8156-16.1.60 A three-part assignment that teaches inner, left, right, and full outer joins in several tools, and that asks students to select, use, and validate joins. It is a good next step for anyone who wants to see how the join types of this chapter appear outside SQL.

10.21 References

Datar, S. M., & Rajan, M. V. (2021). Horngren’s cost accounting: A managerial emphasis (17th ed.). Pearson. https://www.pearson.com/en-us/pearsonplus/p/9780136713845

SQLite. (n.d.-a). Quirks, caveats, and gotchas in SQLite. https://www.sqlite.org/quirks.html

SQLite. (n.d.-b). SELECT. https://www.sqlite.org/lang_select.html