Keep Each Order Total Correct

Introduction

PAPER & PIER sells small goods from two market stalls. Before closing, its desk needs to match each order's item value to money actually received and refunded. One order can contain several items and several real payments. Joining those raw lists carelessly can make an honestly paid order look wrong.

You will build three reusable SQLite views that total each kind of fact separately. You need basic terminal navigation and SQL SELECT/WHERE knowledge. Aggregates, joins, views and missing values are explained here. SQLite and a synthetic ledger are prepared; use Terminal throughout.

Keep all original tables, IDs and values. You may create or replace only item_totals, payment_totals and refund_totals views and save their definitions in totals.sql. Amounts are integer cents; this is a simplified receipt reconciliation, without taxes, payment-provider settlement or currency conversion.

Keep One Item Total per Order

In this step, you will read a normal order, see how a raw join duplicates money, and save an accurate item total view. Begin in Terminal. cd changes directory; this absolute path also recovers from entering a wrong directory:

cd /home/labex/project/sales-desk

sqlite3 opens the named database. -header prints column names, -column aligns values, and the quoted SQL runs before the connection closes. Quotes keep the SQL's spaces and semicolons in one shell argument. Read the prepared order identities:

sqlite3 -header -column ledger.db "SELECT * FROM orders ORDER BY order_id;"

There are four orders. A1 and A2 belong to different orders even though both customer labels say Alex Kim. Group by the order ID, never by the customer's name. Read A1's item lines and payment events:

sqlite3 -header -column ledger.db "SELECT * FROM items WHERE order_id = 'A1'; SELECT * FROM payments WHERE order_id = 'A1';"

Its items cost 300 + 500 = 800 cents. Payments P1 and P2 are two distinct, successful 400-cent events, also totaling 800. P3 failed and contributed no money. succeeded means the event counts in this project's arithmetic; pending and failed do not.

A join combines related rows. AS assigns a short table alias: i means items and p means payments. ON specifies matching order IDs. Before totaling anything, see every matched pair for A1:

sqlite3 -header -column ledger.db "
SELECT i.item_id, i.unit_cents, p.payment_id, p.amount_cents
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded'
ORDER BY i.item_id, p.payment_id;"

There are four rows: each of the two items matches both payments. This is real join behavior. It does not mean four items or four payments occurred. SUM adds the expression over its input rows. On this duplicated input, both sums are wrong:

sqlite3 -header -column ledger.db "
SELECT SUM(i.quantity * i.unit_cents) AS joined_due,
       SUM(p.amount_cents) AS joined_paid
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded';"

Both displayed totals are 1600, twice the real amount. DISTINCT keeps different values, not different payment identities. It cannot repair this problem:

sqlite3 -header -column ledger.db "
SELECT SUM(DISTINCT amount_cents) AS wrong_paid
FROM payments WHERE order_id = 'A1' AND status = 'succeeded';"

The result is 400, dropping one legitimate equal-value payment. Instead, total each fact list on its own. GROUP BY order_id makes one result row for each order ID. quantity * unit_cents calculates each item's value before addition. The resulting grain, meaning what one output row represents, is one order.

A view is a named SELECT stored in the database. Reading it runs that SELECT against current source rows; it does not copy or replace the items. Save its definition in totals.sql. In the shell, cat writes the following literal here-document to the file; > replaces its old content. Paste the final SQL alone on a line. This block belongs at the shell prompt, not inside an interactive SQLite prompt:

cat > totals.sql <<'SQL'
DROP VIEW IF EXISTS item_totals;
CREATE VIEW item_totals AS
SELECT order_id, SUM(quantity * unit_cents) AS due_cents
FROM items
GROUP BY order_id;
SQL

DROP VIEW IF EXISTS removes only an earlier definition of this view, if present. It leaves source tables and data intact. CREATE VIEW saves the new definition. < supplies the saved file as SQLite's input:

sqlite3 ledger.db < totals.sql

No output is expected when that succeeds. Read the actual view:

sqlite3 -header -column ledger.db "SELECT * FROM item_totals ORDER BY order_id;"

Expect A1 = 800, A2 = 250, A3 = 500 and A4 = 200 cents, each in one row. A3's quantity of two is included. You now have a useful first artifact: a saved view definition that can run on another compatible ledger.

If a file is missing, ls lists this directory's files. Check the absolute cd and the spelling totals.sql. A SQL syntax error identifies the failing statement; repeat the complete creation block to replace a mistyped file, then apply it again. If the shell is still waiting after the here-document, enter the missing final SQL line. Do not delete the ledger to recover.

Combine Independent Money Totals

In this step, you will add successful payment and refund totals to the saved script, then combine the three one-row-per-order facts while retaining unpaid orders. Begin in Terminal at /home/labex/project/sales-desk.

The same aggregation rule applies to payments and refunds: each genuine event counts, including equal amounts with different IDs. Filter status = 'succeeded' inside each fact's SELECT, before grouping. A pending refund must not reduce received money. Read the refund evidence:

sqlite3 -header -column ledger.db "SELECT * FROM refunds ORDER BY refund_id;"

A3 has two separate successful 50-cent refunds and a pending 250-cent refund. Its actual refund total is 100, not 50 and not 350.

Append the other two view definitions. >> adds to the end of an existing file instead of replacing the item view. If repeating this append while recovering from a mistake, first rebuild totals.sql using the previous step's complete block, then append this block once:

cat >> totals.sql <<'SQL'
DROP VIEW IF EXISTS payment_totals;
CREATE VIEW payment_totals AS
SELECT order_id, SUM(amount_cents) AS paid_cents
FROM payments
WHERE status = 'succeeded'
GROUP BY order_id;

DROP VIEW IF EXISTS refund_totals;
CREATE VIEW refund_totals AS
SELECT order_id, SUM(amount_cents) AS refunded_cents
FROM refunds
WHERE status = 'succeeded'
GROUP BY order_id;
SQL

sqlite3 ledger.db < totals.sql

The saved script can be reapplied: each definition is dropped and recreated from unchanged source facts. Read the money views:

sqlite3 -header -column ledger.db "SELECT * FROM payment_totals ORDER BY order_id; SELECT * FROM refund_totals ORDER BY order_id;"

Payments have A1 = 800, A3 = 600 and A4 = 250. Refunds have only A3 = 100. A2 is absent from both because no eligible payment or refund exists. Absence from a fact total must not remove the order from reconciliation.

LEFT JOIN retains every row on its left even when no right-hand match exists. Start with orders, then join each already aggregated view by order ID. A missing match supplies SQL NULL, meaning no value, rather than zero. COALESCE(value, 0) substitutes zero only when that total is NULL. Conditions on event status already belong inside the money views; adding a WHERE condition on a missing joined row could discard the unpaid order.

sqlite3 -header -column ledger.db "
SELECT o.order_id, o.customer,
       COALESCE(i.due_cents, 0) AS due_cents,
       COALESCE(p.paid_cents, 0) AS paid_cents,
       COALESCE(r.refunded_cents, 0) AS refunded_cents
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
ORDER BY o.order_id;"

Read each row against the normal business:

Each fact view has at most one row per order before the joins, so one fact cannot multiply another. Keep the original four tables and all their records; the three views and totals.sql are the only database definitions and file you need to add. The next lab starts from its own fresh ledger and turns this reasoning into a complete reconciliation query.

Summary

You exposed two money-total traps, saved reusable one-row-per-order fact views, and combined them with LEFT JOIN and COALESCE. Separate equal-value events remained real events, and unpaid orders stayed visible.