G-XR8P2XJ088

AI-Written SQL: Keep Each Order Counted Once

An AI-assisted sales report can run successfully and still count the same order more than once. The problem may sit in the step that combines orders with their individual items. A query can follow the database’s rules perfectly while producing a total that answers the wrong business question.

For an independent creator or business owner, the useful review begins before the final figure: decide what one row should represent. Then check whether that meaning survives every combination of records. A small worked example makes the issue visible without requiring a large database or an advanced programming background.

Write the reporting rule first

Suppose a fictional digital-product studio wants one row per paid order, showing its recorded order total and its number of line items. Both orders in our example were paid in pounds during the reporting period. The requested overall figure is the sum of those recorded order totals.

There are two source tables. The orders table contains one record per order ID. The items table contains one record per line item, with an order ID connecting it to its order. Multiple item records for the same order are expected: buying two products does not create two separate orders.

If the starting point is an exported file, preserve those identifiers before asking AI to combine records. MaryChuks.com’s guide to keeping product codes intact during spreadsheet cleaning explains why the characters in an identifier deserve explicit handling.

See what the join produces

A join combines records according to a matching rule. The PostgreSQL 18 documentation on joined tables explains that an inner join produces a row for each matching row on the other side. An order with two matching items therefore appears twice in an order-to-item result.

That repeated order is useful when examining individual items. The difficulty starts when a calculation treats the repeated order total as a new amount on every resulting row. The data has moved to an item-level view while the amount still describes the whole order.

Here is the fictional source data:

  • Order A101: recorded total £75, with two line items.
  • Order A102: recorded total £75, with one line item.

The correct order-total sum is £150. Joining each order directly to its items creates three rows: A101 paired with its first item, A101 paired with its second item, and A102 paired with its single item. The order-total column now contains £75, £75 and £75.

Adding that column produces £225. The extra £75 comes from counting A101’s recorded total again. It does not represent another paid order. This example is deliberately small enough to reconcile by hand; its purpose is to expose the relationship between records.

Equal amounts are still different orders

A request to “remove the duplicates” leaves a crucial choice unresolved: duplicates of what? PostgreSQL’s aggregate-expression documentation distinguishes aggregation over input rows from aggregation over distinct values. In SUM(DISTINCT order_total), the distinction applies to the amount values, rather than to order identity.

In our example, the only distinct amount is £75. That attempted repair returns £75, losing one legitimate order’s contribution. A101 and A102 remain different orders even though their totals match. The reporting rule requires both to contribute once.

Counting distinct order IDs also answers only part of the question. On the three joined rows, that count is correctly two, while a straightforward sum of the repeated order-total column remains £225. A correct order count cannot, by itself, validate the money column.

Bring the detail to the required level

For this particular report, first summarise the items into one row per order ID, containing its item count. PostgreSQL’s aggregate-function tutorial shows how grouping allows an aggregate to be calculated separately for each group. Here the grouping field would be the order ID.

Join that item-count summary to the orders table. Provided each side has one row per order ID, the result has one row for A101 with a count of two, and one row for A102 with a count of one. Each £75 order total appears once, and their sum is £150.

This is a proposed design for the stated order-level report. A report about the amounts attributable to individual products needs its own item-level amounts and allocation rules. Establish that separate definition before adapting the query; repeating a whole-order total beside every product would recreate the original problem.

Decide how missing matches should appear

Add a further practice case: an order exists, but its item records are absent. An inner join would omit that order. A left join starting from orders can retain it, with missing values on the item-summary side. This distinction is also documented in PostgreSQL’s joined-table guidance.

For our proposed report, retain the order and flag the missing detail for review. Do not silently equate missing item records with a verified item count of zero. The business should decide how incomplete records affect a report before the assistant chooses an exclusion or substitutes a value.

Ask AI for a reviewable query

Give the assistant the table definitions and the reporting rule, then request an explanation before execution. For example: “Prepare a read-only query returning one row per paid order ID, its recorded total and a line-item count. Summarise the item records by order ID before joining. Keep orders with missing detail visible and flag them. Explain every matching key and show intermediate rows for this fictional example.”

Ask it to state any assumptions about unique order IDs, the reporting period and currency. Examine the generated query in the database system you actually use. The worked arithmetic here establishes the expected result for this fixture; it does not certify every generated query or a different business schema.

Check the rows as well as the total

  • Before combining: confirm two qualifying orders and a recorded-total sum of £150.
  • After combining: inspect both order IDs and require exactly one report row for each.
  • Keep equal totals: ensure both £75 orders contribute to the final £150.
  • Check exceptions: make a missing-item case visible, and repeat the row check after any further join.

Keep the reporting definition, query and expected example together. If a later edit adds a product category, customer tag or another relationship, recheck what each resulting row represents. AI assistance is more useful when the business can explain how every source order contributes to the final figure.

Sources

Documentation checked on 25 October 2026. The studio, orders, amounts and suggested AI instruction are illustrative.


Discover more from Marychuks.com AI, Psychology, Business & CreativeVerse

Subscribe to get the latest posts sent to your email.

3 thoughts on “AI-Written SQL: Keep Each Order Counted Once”

Leave a Reply

Discover more from Marychuks.com AI, Psychology, Business & CreativeVerse

Subscribe now to keep reading and get access to the full archive.

Continue reading