Combining finance queries with UNION can remove legitimate transactions

If two source reports reconcile separately but their combined report is short, inspect the operation that combines them. A query can remove rows without reporting an error when the selected columns make different transactions appear identical. Compare each branch's record count and amount with the result immediately after combination, before looking for missing activity in the source systems.

PostgreSQL documents that UNION eliminates duplicate result rows unless UNION ALL is used. The comparison concerns the columns returned by the queries. If those columns omit transaction identity, two legitimate invoices with equal visible attributes can collapse into one row. The database is following the requested set operation; it has not determined that the underlying business events were duplicates.

Record what each branch represents: a company, a source system, a transaction type or a period. Then state whether those populations are intended to be disjoint or can overlap. That answer determines the appropriate combination rule. Switching operators before understanding the populations can replace an understatement with duplicate counting.

Consider two fictional companies, each with a separate $500 invoice dated April 12 for a category called consulting. If both branch queries select only date, category and amount, each produces the same three displayed values. UNION can return a single $500 row. The intended combined population contains two invoices totaling $1,000, so $500 has disappeared from the report without any source transaction being deleted.

Add the company and stable invoice reference to the diagnostic output. The two business events are now distinguishable to the reviewer. This does not mean every final summary must display internal identifiers. It means the processing layer needs sufficient identity and provenance to account for the records before deliberate aggregation removes detail.

Look for equal records within a branch as well as across branches. Two separate transactions from one company can share the same date, category and amount. A repair that adds only a company label may solve the demonstration while leaving legitimate same-company records vulnerable to elimination. The row definition must reflect the transaction grain the report actually needs.

Where two validated populations are disjoint, an append that preserves every row may fit the requirement. UNION ALL provides that behavior for the query combination described in PostgreSQL's documentation. Finance should still verify that each branch contains the intended records once and that the resulting amount is calculated at the correct grain.

If a current table and an archive both contain the same historical invoices, preserving every row would double-count the overlap. Establish the authoritative version and the boundary between sources. Resolve genuine duplicate identities using a documented rule, such as a verified source priority or record version, before presenting the combined population as complete.

Do not use equality of amount as the overlap rule. Two independent $500 invoices belong in the total, while two copies of one $500 invoice require reconciliation. Likewise, an amended copy of an invoice may have a different amount but still represent the same underlying record. Identity and version evidence answer that question more reliably than whether the selected display fields happen to agree.

The combined queries need compatible column counts and types, as the PostgreSQL documentation explains. Compatibility does not establish that corresponding columns have the same business meaning. Check the position and definition of each selected field, especially where one branch labels a gross amount and another supplies a net amount under a similar heading.

Retain currency, entity and transaction-type context until the approved reporting calculation handles them. Adding amounts from different currencies without a conversion rule can produce a numerically tidy but meaningless total. A UNION repair should not distract the reviewer from another mismatch that was already present between the branches.

Inspect filters and status rules separately. One branch might include voided invoices while another includes only active records. A third might select invoice lines while the first two select headers. Write the intended population and grain beside each branch so the combined query becomes a reviewable calculation rather than a series of visually similar SELECT statements.

Create a controlled set containing two separate transactions with identical display fields and a genuine repeated copy of one transaction. Include one revised version if the source design supports revisions. Agree which records should survive and how much they should contribute. This prevents a blanket deduplication rule from looking correct merely because the test data never includes legitimate equal values.

For a simple extension of the example, add an independent $200 invoice. The intended distinct business population is $500 plus $500 plus $200, totaling $1,200. If a duplicate copy of the first invoice is also present, it should not increase the intended total to $1,700. The repair needs to preserve the second legitimate $500 while resolving the extra copy of the first.

Have the analyst show the provenance of every retained and excluded row in that small set. Then reconcile the affected production report using a fixed, read-only population. Compare counts, identities and monetary totals before and after the repair. A correct grand total reached through offsetting missing and duplicated records is insufficient evidence.

If the defect was confined to the reporting query, keep the underlying invoices intact. Identify which distributed outputs used the understated combination and give the reporting owner the quantified difference. Any separate ledger correction needs its own supporting accounting reason; it should not be invented to compensate for a query that omitted valid rows.

Retain the branch definitions, combination rule and regression example with the report. When a new company or source is added, verify its identity scope and overlap before including it. The recurring control is a clear account of what one retained row represents and why every legitimate transaction contributes exactly as intended.