Chapter 10: Joining and Summarizing Data in SQL

Accounting Analytics: An Integrated Approach

In this chapter

Part III of V: Accounting Analytics with SQL

Chapter 2 of 4 in this Part

• 10.1 Why Join Tables
• 10.2 Inner Joins
• 10.3 Following the Keys
• 10.4 Keeping Unmatched Rows with LEFT JOIN
• 10.5 Summarizing with Aggregate Functions
• 10.6 Filtering Groups with HAVING

• 10.7 Profiling a Table Before Using It
• Guided Tutorial 10.1
• 10.9 Fan-Out and Double Counting
• 10.10 Subqueries
• 10.11 The Order in Which a Query Runs
• Guided Tutorial 10.2
• 10.13 Measuring the Plant Against Its Standards
• Guided Tutorial 10.3
• 10.15 Looking Ahead

This chapter combines tables and summarizes their rows to answer the controller’s three follow-up questions. Each module follows a section of the chapter, and the three guided tutorials extend one saved script, Chapter10.sql.

Learning objectives

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.

The first three objectives cover joins, left joins and anti-joins, and aggregates with GROUP BY and HAVING, including profiling a table.

Learning objectives (continued)

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.

The last three cover fan-out, subqueries, and the accounting work they make possible: a trial balance from the ledger and the plant measured against its standards.

Which products, which years, volume or cost?

• Chapter 9 found the variance recorded at each work order’s close, mostly overhead
• Which products carry it? How much arose in each year?
• Is it volume, fewer units to spread costs over, or the cost itself?
• Together they need summaries and joins: product, hours, output and amounts live apart

The product is recorded on the work order, the hours in the labor time and the payroll, the output in the completions, and the amounts in a ledger that identifies accounts only by their AccountID. Answering means joining tables on the keys of Chapter 3 and summarizing thousands of rows into a few totals.

10.1 Why Join Tables

A join follows the links between tables

• A database stores each fact once and links the tables through keys
• A close names its work order; the work order names its item; the item its group
• No foreign keys are declared, so nothing guarantees that the keys match
• A join on the wrong keys raises no error and may keep a plausible count, so test keys, matches and totals

A join is the SQL version of Power Query’s Merge from Chapter 5: Inner keeps the matched rows, Left Outer keeps every row of the first query, and Left Anti keeps the rows with no match. XLOOKUP behaves like a left join that brings back one column, as long as its key 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 keeps the matched pairs

SELECT woc.WorkOrderCloseID, 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;

• Aliases such as woc keep a query short; qualify every column in a join
• Every close has one work order and every work order one item, so the rows must equal the closes

A column whose name appears in both tables must be qualified, or the query stops with “ambiguous column name”. If the join returns fewer rows than the WorkOrderClose table holds, some closes point to a work order or item that does not exist, and the inner join has dropped them without a word. WorkOrderID and ItemID are primary keys, so an unchanged count shows that no close was lost or repeated. On a key that is not unique, a lost row and a repeated one could cancel.

Only the work orders that have a close

Figure 10.1 · How Inner and Left Joins Match Rows (detail)

Four work orders, three of them closed: the inner join on WorkOrderID returns the three that have a close. The released work order has no match and does not appear, which is the behavior the next modules build on.

10.3 Following the Keys

Join paths through purchasing

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

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. These paths reach beyond the sales and ledger tables of Part II, into the Procure-to-Pay group. A purchase order names two employees, its creator and its approver, so each role is a join of its own, with two aliases of Employee.

Join paths through manufacturing

Table 10.1 · Join paths through the purchasing, manufacturing, payroll, and ledger tables

From	To	Join on
WorkOrder	Item	ItemID
WorkOrderOperation	WorkOrder	WorkOrderID
WorkOrderOperation	WorkCenter	WorkCenterID
WorkOrderClose	WorkOrder	WorkOrderID
ProductionCompletionLine	ProductionCompletion	ProductionCompletionID
ProductionCompletionLine	Item	ItemID
LaborTimeEntry	WorkOrderOperation	WorkOrderOperationID

These are the paths of this chapter’s tutorials: closes to work orders to items, operations to work centers, and completion lines to the items they made. Chapter 3’s manufacturing figure traced the same tables.

Join paths through payroll and the ledger

Table 10.1 · Join paths through the purchasing, manufacturing, payroll, and ledger tables

From	To	Join on
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

The payroll tables link through the register to its period, employee and cost center. The last row is different from the others: the ledger reaches its documents through two columns, not one key.

A join to the ledger names the document type too

• SourceDocumentType names the kind of document, usually a table, and SourceDocumentID its key
• Each document table numbers its own rows, so one ID belongs to many kinds of document
• So the join’s condition includes the type, such as gl.SourceDocumentType = 'JournalEntry'
• Without it, ledger rows pair with whichever document happens to share their number

The ledger rows with one SourceDocumentID come from several kinds of document, of which the traced sale of Tutorial 3.2 is only one. With the type in the condition, a join to JournalEntry finds the one journal entry posted to the variance account in the year: the closing entry of Chapter 9. PayrollSummary is the exception Chapter 3 named, a summary of a pay period by cost center whose ID is a code, not the key of a table.

10.4 Keeping Unmatched Rows with LEFT JOIN

A left join keeps every row of the first table

Figure 10.1 · How Inner and Left Joins Match Rows (detail)

The left join keeps all four work orders, with NULL in the close columns where no close exists. Keeping only the rows where the second table’s key IS NULL turns it into an anti-join, which lists exactly the work orders with no close.

Anti-joins find what is missing; NOT IN can fail

• An anti-join keeps the left join’s rows whose matched key IS NULL
• NOT EXISTS asks the same thing with a correlated subquery
• NOT IN keeps no row when its list holds a NULL, because none can be shown to be absent
• So use NOT EXISTS or the anti-join when the column can be NULL

A few requisitions have no approver, so a NOT IN test of employees against the approvers returns zero, while NOT EXISTS and the anti-join find nearly every employee. Anti-joins are among the most useful accounting tests: existence tests start from the ledger, completeness tests from the documents, and Chapter 12 builds a library of them.

Watch out: WHERE can undo a left join

• A condition on the second table in WHERE removes the unmatched rows
• A NULL Status never equals Closed, so items without work orders vanish
• Move such a condition into the ON clause, where it limits the matches
• The IS NULL test of an anti-join is the exception: it stays in WHERE

When a 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. That is the difference Chapter 3 warned about between a list of customers built from their orders and a list of all customers.

Check your understanding

Which join answers each question?

1. Every manufactured item, with its work orders if it has any
2. The work orders that have never closed
3. Each close with its item’s group
4. The employees who never approved a requisition, when some requisitions have no approver

Ask students what the wrong choice would return in each case.

Each question has its join

1. A left join from Item
2. An anti-join: a left join keeping rows whose close IS NULL
3. Inner joins from the close through the work order to the item
4. NOT EXISTS or an anti-join, never NOT IN

The fourth is the trap: with a NULL in the list, NOT IN returns no rows at all, and nothing warns you.

10.5 Summarizing with Aggregate Functions

Aggregates and how they treat missing values

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

Without GROUP BY, an aggregate summarizes every row the query keeps. GROUP_CONCAT joins a group’s values into one text, which makes a short list of exceptions easy to read.

GROUP BY returns one row per group

Figure 10.2 · GROUP BY Collapses Rows into One Row per Group

In this book, every column in the SELECT list is either named in GROUP BY or wrapped in an aggregate. SQLite accepts a bare column and fills it from one of the group’s rows, which says nothing about the group, so treat it as an error. After a left join, count the second table’s key rather than every row, and wrap a SUM in COALESCE to show a zero; GROUP BY does what a PivotTable’s Rows area does.

Watch out: group by the key, not the name

• Two different Charles River customers share one name
• Several items share their names with other items
• Grouping by name merges them into one row, and nothing in the result says so
• Group by the ID, and add the name to GROUP BY if you want to show it

The same rule applies to accounts, employees and suppliers: names are labels, keys are identities.

10.6 Filtering Groups with HAVING

WHERE filters rows; HAVING filters groups

• WHERE runs before any group exists, so it cannot use an aggregate
• An aggregate in WHERE stops with “misuse of aggregate”
• HAVING runs after the aggregates, so its condition can use them
• Filter rows in WHERE where you can, so fewer rows reach the grouping step

The two often work together: in Tutorial 10.1, WHERE keeps the year’s closes and HAVING keeps the items whose total variance passes a threshold. Listing only the accounts with a balance is another HAVING condition.

Check your understanding

WHERE or HAVING?

1. Keep the closes of one year
2. Keep the items whose total variance passes a threshold
3. Keep the accounts whose balance is not zero
4. Keep the ledger rows of one account

Ask students whether each condition is about one row or about a group’s total.

Rows in WHERE, groups in HAVING

1. WHERE: a date is a property of each row
2. HAVING: a total exists only after grouping
3. HAVING: a balance is a sum of rows
4. WHERE: an account is a property of each row

A query can use both, and Tutorial 10.1 does: WHERE for the year, HAVING for the threshold.

10.7 Profiling a Table Before Using It

Data quality tests in SQL

Table 10.3 · Data quality tests in SQL

Dimension	SQL techniques
Accuracy	MIN and MAX of amounts; a WHERE condition that compares two dates; GROUP BY the key with HAVING COUNT(*) > 1
Completeness	COUNT(column) against COUNT(*); COUNT(*) by month; GROUP BY a type column
Consistency	GROUP BY two related columns; an anti-join to the parent table; the distinct values of a code column
Timeliness	MIN and MAX of the dates; COUNT(*) by month at each end

Each row gives the SQL for the tests of one data quality dimension from Chapter 2, such as plausible ranges, blanks in required fields, matching references and the date range of the extract. The chapter’s table lists those tests beside the techniques.

A profile tells you what a table holds

• One query counts the rows, the distinct numbers, the filled columns and the dates
• Work order numbers are unique, and every closed work order has a close date
• No other work order has one, so the status and the date tell one story
• A clean profile is a finding: record it with the script

A data profile is a short set of queries that describes one table before you rely on it. 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.

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

Join the variance to its products in six steps

1. Join the closes to their work orders, and test the join
2. Add the items
3. Total the variance of fiscal 2026 by item group, and check it against the ledger
4. Compare the three years
5. Keep the items that carry the most variance
6. List every manufactured item, including those with no closes

Students join the closes to their work orders and items, testing each join by its row count, total the year’s variance by item group and check it against the ledger, compare the three years, keep the items above a threshold with HAVING, and list every manufactured item with a left join.

Furniture’s variance rose as its closes fell

Figure 10.3 · The Manufacturing Variance by Item Group and Year (detail)

Furniture carries most of the variance, and its variance rose in each year while its number of closes fell. HAVING then keeps only a handful of items above the threshold, spread across several product types.

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;

The joins keep every close only because every key matches, and the totals by group add up to the ledger. Furniture’s rising variance with falling closes is the pattern to explain.

Checkpoint (continued)

• 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: a ranking by total dollars shows where the amounts accumulate, not which items are made inefficiently. It rises in the groups the plant makes most while the number of work orders falls, which turns the question toward the cost of production itself.

10.9 Fan-Out and Double Counting

A header amount repeats on every line

Figure 10.4 · Fan-Out: A Header Amount Repeated on Every Line

An invoice with three lines appears three times in a join of SalesInvoice and SalesInvoiceLine, and so does its GrandTotal. Summed over the year, the join nearly doubles the invoice total: the double counting Chapter 3 warned about. The lines add up to the SubTotal; the GrandTotal also holds the header’s freight and tax.

Add up each amount at its own level

• Sum GrandTotal over SalesInvoice alone, and LineTotal over its lines
• Closes joined to their operations repeat each variance three or four times
• A count of a join’s rows counts lines, not headers: COUNT(DISTINCT) counts headers
• Summarize the many side first, then join one row per header

The derived tables of the next module show how to summarize first. The rule is the same in Power BI’s data models later in the book: an amount belongs to the table it is recorded in.

Check your understanding

What is wrong with each result?

1. GrandTotal summed over invoices joined to their lines
2. The rows of that join reported as the number of invoices
3. The variance summed over closes joined to their operations
4. Revenue totals grouped by customer name

Ask students how they would have noticed each one with a control total.

Fan-out and grouping errors

1. Fan-out: each invoice’s total repeats once per line
2. It counts lines: use COUNT(DISTINCT) on the invoice key
3. Fan-out: each variance repeats once per operation
4. Two customers with one name are merged: group by the key

A control total from the header table, such as the sum of GrandTotal over SalesInvoice alone, exposes the first three at once.

10.10 Subqueries

Subqueries answer two-step questions in one

• A scalar subquery returns one value, such as the year’s average variance
• It can stand wherever a value can, including the SELECT list, side by side
• A subquery after IN supplies a list, such as the work orders of Furniture items
• No row: NULL. Several rows: SQLite takes the first; most systems stop

Tutorial 10.2 puts two scalar subqueries side by side to tie the closes to the ledger. A subquery is written inside another query, in parentheses.

A correlated subquery runs once per outer row

• It refers to a column of the outer query, such as each account’s ID
• NOT EXISTS is one; a count of each account’s postings is another
• With an index on the searched column, it is quick
• Without one, over thousands of rows, it can take minutes, and a join is often faster

GLEntry has an index on AccountID and FiscalYear, so counting each account’s postings is fast. For a large table without an index, a join to a derived table is often faster, so test the query rather than assume.

Derived tables summarize before they join

• A subquery in FROM is a table of its own: give it an alias
• Sum each invoice’s lines in a derived table, one row per invoice
• Join that to the invoices, and nothing repeats
• The same pattern combines output by year with hours by year

With the lines summed first, the sum of the invoices’ SubTotals equals the sum of their lines. Tutorial 10.3 joins two derived tables on the year to compare the hours recorded with the standard hours of output.

10.11 The Order in Which a Query Runs

Written in one order, evaluated in another

Figure 10.5 · The Order in Which a Query Is Written and Evaluated

The database assembles the rows from FROM and its joins, keeps those that meet WHERE, forms the groups, keeps those that meet HAVING, calculates the SELECT list, and finally sorts and limits. It may take faster shortcuts, but the result is always the one this logical order describes.

The order explains the rules

• WHERE runs before grouping, so it cannot use an aggregate; HAVING can
• ORDER BY runs after SELECT, so it can sort by an alias
• By the same logic, GROUP BY should not use an alias, but SQLite allows it
• PostgreSQL and MySQL also allow it; SQL Server does not

The book writes GROUP BY CloseYear because it is easier to read than repeating the expression; in SQL Server you would repeat the expression with its own function, SUBSTRING. SQLite also tolerates an alias in WHERE, a habit to avoid.

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

Follow the cost through the ledger in six steps

1. Put the closes and the ledger side by side
2. Build the pre-closing trial balance
3. Check that debits equal credits
4. Follow account 1090 by year and source
5. Split the conversion variance into labor and overhead
6. Identify the journal entries

Students tie the closes to the ledger, rebuild the pre-closing trial balance, check that debits equal credits, follow the clearing account by year and source, split the conversion variance into labor and overhead, and identify the journal entries.

The ledger rebuilds the pre-closing trial balance

Figure 10.6 · The Pre-Closing Trial Balance of Fiscal 2026 (detail)

The query keeps the postings through the year-end without the year’s two closing entries and groups them by account. It returns the same accounts and balances as Chapter 6’s trial balance, and the net of all its balances is zero: the two reconstructions agree, which does not show that the entries behind them are complete or correctly classified.

Conversion cost flows through the clearing account

Figure 10.7 · The Flow of Conversion Cost Through Account 1090 (detail)

The clearing account receives the actual cost of conversion, mostly manufacturing payroll plus factory overhead and depreciation entries. It releases the standard conversion cost of the units completed to finished goods, and the work order closes clear what is left to the variance account.

The overhead part grew while the labor part shrank

Figure 10.7 · The Flow of Conversion Cost Through Account 1090 (detail)

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; the overhead part grew.

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 two scalar subqueries tie the closes to the ledger, and the trial balance matches Chapter 6’s account for account. The clearing account’s sources and releases are the flow the next items measure.

Checkpoint (continued)

• 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

Standard hours allowed measure direct labor

• Compare the inputs used with the inputs the standards allow for the output achieved
• Standard hours allowed: units completed times each unit’s standard labor hours
• Compare them with the direct labor time recorded
• It is Chapter 7’s flexible budget applied to hours

A standard cost system recalculates the standard at the output actually achieved before comparing it with the actual (Datar and Rajan). A labor efficiency variance compares the hours allowed with the direct hours used on that output, at the standard rate. Tutorial 10.3’s yearly ratio is a simpler comparison, an observed-hours diagnostic.

Indirect time is overhead

• Indirect labor is time no single work order carries
• In Charles River’s records it reaches the variance through overhead
• Fixed overhead is absorbed only by units made: output below plan leaves some unabsorbed
• LaborType separates direct, indirect and non-manufacturing time

The unabsorbed fixed overhead is the production-volume variance (Datar and Rajan). A payroll period belongs to the fiscal year of its pay date, so the period that ends in late December is counted in the next year, which is why Tutorial 10.3 measures hours with the labor time entries and uses the payroll for what they cost.

The Payroll and Time tables

Figure 10.8 · The Payroll and Time Tables

Time clock entries record each employee’s day; labor time entries split it by labor type and, for direct labor, link to a work order operation. Payroll registers group by period and hold their lines and payments; Employee, CostCenter and WorkOrderOperation come from other table groups.

Guided Tutorial 10.3: Comparing Output with the Hours It Took

Compare output with the hours in eight steps

1. Measure the output and its standard hours
2. Measure the hours and pay of the manufacturing payroll
3. Count the people
4. Profile the two tables before comparing them
5. Find why the time records end early
6. Compare the hours recorded with the standard hours, by kind of labor
7. Test the time that has no work order operation
8. Complete and save the script

Students measure the output and its standard hours, the payroll’s hours and pay, and the number of people, then profile the two tables, find why the time records end early, and compare the hours recorded with the standard hours by kind of labor.

Two derived tables meet on the year

Figure 10.9 · Hours per Standard Hour by Labor Type and Year (detail)

The first derived table sums the hours by year and labor type, so it holds two rows a year. The second sums the standard hours of the output by year, counting completions only up to the last day with time records, so it holds one row a year. Each hours row meets exactly one output row, which gives six rows. The standard hours repeat on the two rows of a year, so they must not be added across the labor types.

Direct time stayed below the standard hours

Figure 10.9 · Hours per Standard Hour by Labor Type and Year (detail)

The direct hours recorded stayed below the standard hours in every year, while indirect time per standard hour grew. The profiles had shown that the time records end before the year does, so the output is counted only up to that day. Indirect time ends a week earlier still, on December 4, 2026, so the ratio is an observed-records diagnostic. Counted to that day on both sides, the 2026 direct ratio is somewhat lower and the indirect ratio a little higher, and the pattern of the years holds.

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;

Output and its standard hours moved together, while the payroll paid the same number of people, so not a larger workforce, with far more overtime. The profiles explain why the output is cut off at the last day with time records.

Checkpoint (continued)

• 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.

COUNT of rows counts registers, and COUNT DISTINCT of employees counts people. A few direct labor entries have no valid operation, a small finding to record.

10.15 Looking Ahead

Back to the controller’s questions

• Products: the variance rises where the plant makes most, while closes fall
• Where: conversion cost the standard did not absorb, mostly overhead
• Volume: output was about the same, so the records point to cost
• But indirect time grew by about two-thirds and overtime more than doubled

The direct hours recorded stayed below the standard hours. Indirect labor is overhead, so its growth is consistent with the growth of the overhead part, while the favorable labor part shrank as direct hours moved toward the standard. The comparison is an observed-hours diagnostic, not a labor efficiency variance or a measure of the production-volume variance, and the queries do not yet measure how much each cause explains. Next, the controller needs a measure she can follow month by month, which Chapter 11 builds.

Summary

• Joins follow logical keys, tested by their keys, row counts and totals
• Left joins keep whole populations; anti-joins and NOT EXISTS find what is missing
• Aggregates with GROUP BY and HAVING summarize by key, after profiling the table
• Fan-out repeats header amounts: sum each amount at its own level
• Subqueries and derived tables answer questions that take more than one step

Most of this chapter’s queries were diagnostic analytics: they narrowed the search for why the variance grew, by breaking it down by product, year and part, and by comparing hours with the standard hours of the output.

Key terms

Aggregate function · Anti-join · Correlated subquery · Data profile · Derived table · Fan-out · GROUP BY clause · HAVING · Inner join · Join · Left outer join · Logical evaluation order · NOT EXISTS · Production-volume variance · Scalar subquery · Standard hours allowed · Subquery · Table alias · Trial balance

Every term is defined at the end of the chapter. Fan-out, anti-join and standard hours allowed are the ideas the rest of Part III relies on most.

Practice and next steps

• Financial Accounting
  • Exercise 10.1: Reconciling Invoice Lines to the Ledger in SQL
  • Exercise 10.2: Tracing a Sale from the Ledger to Its Documents
• Managerial Accounting
  • Exercise 10.3: Average Order Value by Customer
  • Exercise 10.4: Purchase Price Variance by Supplier
• Auditing
  • Exercise 10.5: Testing Referential Integrity with Anti-Joins
  • Exercise 10.6: Suppliers That Share an Employee’s Address

Next: Chapter 11: Analytical Queries in SQL

Assign one exercise from each perspective; Exercise 10.5 tests referential integrity with anti-joins, and Exercise 10.1 reconciles the invoice lines to the ledger. Chapter 11 builds the monthly measure.

About these slides

These slides accompany Accounting Analytics: An Integrated Approach, licensed under CC BY-SA 4.0. Every figure and table comes from the chapter:

https://aa.accountinganalyticshub.com/chapters/10-joining-and-summarizing/chapter.html

Students can find each figure and table, with the full explanation, in the online chapter.