# Join Cardinality in Business Reports: Find Duplicate Rows Before Summing Results

<a id="main-content"></a>

Operations &amp; Systems

October 28, 2026  ·  8 min read  ·  Victor Valentine Romo

On this page

1. [Define One Row on Each Side](<#define-one-row-on-each-side>)
2. [Count the Matches Before Calculating Measures](<#count-the-matches-before-calculating-measures>)
3. [Check the Whole Key and the Snapshot Boundary](<#check-the-whole-key-and-the-snapshot-boundary>)
4. [Reconcile the Inflated Project Sum](<#reconcile-the-inflated-project-sum>)
5. [Summarize Tasks Before Joining Projects](<#summarize-tasks-before-joining-projects>)
6. [Do Not Treat DISTINCT as a Universal Repair](<#do-not-treat-distinct-as-a-universal-repair>)
7. [Copy the Join Review Worksheet](<#copy-the-join-review-worksheet>)
8. [Sources](<#sources>)

The project export totals three thousand dollars. Add the task export, and the report shows five thousand.

Every project identifier is valid. Every task belongs to the right project. The total still changed.

Count the matches before summing the money.

Join cardinality describes how many records match on each side of a join. The worksheet below makes that relationship visible before a report combines measures. It uses fictional exports and proposed review rules. It describes no client engagement or business result.

<a id="define-one-row-on-each-side"></a>

## Define One Row on Each Side

Use two small exports from a fictional service operation. Treat both as complete snapshots for the [same reporting cutoff](</articles/late-arriving-data-report-revisions/>). The project amount is a planning value in dollars. It is neither billed revenue nor a task budget.

One row in the project export represents one project. The project identifier is unique within this snapshot.

| Project ID | Project amount, dollars |
| --- | --- |
| P01 | 1,000 |
| P02 | 1,000 |
| P03 | 1,000 |

One row in the task export represents one task. A project can have several tasks. The task identifier is unique; the project identifier need not be.

| Task ID | Project ID | Planned task hours |
| --- | --- | --- |
| T01 | P01 | 2 |
| T02 | P01 | 3 |
| T03 | P02 | 4 |
| T04 | P02 | 1 |
| T05 | P02 | 2 |

P03 has no task row in this fictional snapshot. That absence says nothing about cancellation or readiness. It establishes only that this export contains no task with project identifier P03.

The project table has three rows and three project keys. The task table has five rows, five task keys, and two project keys. Record those observations separately. Five valid tasks do not imply five distinct projects.

Write the intended output above the query: one row per project, with its project amount and task summary. If the intended output is one row per task, label project amounts as repeated reference values. Do not present their sum as the project total.

Use a [business metric dictionary](</articles/business-metric-dictionary-template/>) to record the counting unit and permitted aggregation. A column name alone cannot establish whether adding its values answers the intended question.

<a id="count-the-matches-before-calculating-measures"></a>

## Count the Matches Before Calculating Measures

PostgreSQL 18 produces one inner-join row for each matching pair. A left join also retains unmatched left rows with [nulls](</articles/null-blank-zero-business-data/>) on the right. These rules appear in the [table-expression documentation](<https://www.postgresql.org/docs/18/queries-table-expressions.html#QUERIES-JOIN>). Apply them to the stated keys before selecting a business measure.

For a one-to-one comparison, imagine a separate project-status summary containing exactly one row for each of P01, P02, and P03. Joining it to the project export produces three matching rows. Uniqueness must hold on both sides under the complete join condition.

For the one-to-many project-to-task join, P01 matches twice and P02 matches three times. P03 matches zero times. The inner join therefore produces five rows. A left join produces six rows: those five matches plus one retained P03 row.

For a many-to-many example, introduce a second synthetic detail export. It contains review rows V01 and V02 for P01, and V03 for P02. Neither detail export is unique on project identifier.

Joining tasks to reviews on project identifier pairs each task with every review for that project. P01 produces two tasks times two reviews, or four pairs. P02 produces three tasks times one review, or three pairs. The inner join produces seven rows.

| Relationship in this example | Join key | Expected inner-join rows |
| --- | --- | --- |
| Projects to one status row per project | Project ID | 3 |
| Projects to tasks | Project ID | 5 |
| Tasks to reviews | Project ID | 7 |

These counts describe the invented snapshots. A relationship label does not supply an expected count for another dataset. Count matches per key and account for the chosen join type.

Keep both detail identifiers in a diagnostic result. The pair T01/V01 differs from T01/V02. Their shared project identifier does not make either row a duplicate record.

<a id="check-the-whole-key-and-the-snapshot-boundary"></a>

## Check the Whole Key and the Snapshot Boundary

Use every field that identifies the intended relationship. If project identifiers restart in each business unit, project identifier alone is incomplete. Ask the export owner whether the unit belongs in the key before accepting a cross-unit match.

Apply the same question to time. A project export containing daily snapshots can repeat a project identifier without containing an invalid row. A task export from one cutoff cannot automatically select the correct project snapshot. Record the [intended date relationship](</articles/event-time-processing-time-business-reports/>) in the worksheet.

For the fictional exports here, identifiers are unique across the operation and both files describe the same cutoff. Those assumptions keep the arithmetic inspectable. They need explicit confirmation in a real report.

Inspect keys that match nothing on either side. A task with project identifier P99 would require a separate exception because the project snapshot contains no P99. Do not manufacture a project amount to complete the report.

Also check [whether blank identifiers are permitted](</articles/csv-data-contract-business-handoffs/>) before joining. A missing key needs a source decision. Substituting a shared placeholder could make unrelated rows appear to belong together.

Retain a small diagnostic view with both source identifiers, the complete join fields, and [the selected source version](</articles/data-lineage-small-business-reports/>). Keep it separate from the presentation table. A reader should be able to trace each unexpected pair without reconstructing the report from a chart.

<a id="reconcile-the-inflated-project-sum"></a>

## Reconcile the Inflated Project Sum

Inspect the inner-join output before hiding columns or building a chart:

| Project ID | Task ID | Project amount, dollars |
| --- | --- | --- |
| P01 | T01 | 1,000 |
| P01 | T02 | 1,000 |
| P02 | T03 | 1,000 |
| P02 | T04 | 1,000 |
| P02 | T05 | 1,000 |

The joined sum is five thousand dollars. The source project total is three thousand. Explain the two-thousand-dollar difference with identifiable contributions.

P01 contributes one thousand dollars originally and two thousand after joining. Its repetition adds one thousand. P02 contributes one thousand originally and three thousand after joining. Its repetition adds two thousand. The inner join removes P03's one thousand because it has no matching task.

The bridge is three thousand plus one thousand plus two thousand minus one thousand, giving five thousand. The residual is zero. Every difference has a visible cause.

A left join would preserve P03 and produce a six-thousand-dollar sum of the repeated project amounts. Preserving unmatched projects answers a coverage question. It does not make a project measure additive at task grain.

Write both effects in the review: repeated amounts and excluded projects. Repairing one effect does not establish that the other is resolved. Keep the original project total as a separate [reconciliation](</articles/rounding-reconciliation-business-reports/>) control.

When the disagreement starts in two finished reports, use the [dashboard reconciliation worksheet](</articles/reconcile-conflicting-business-dashboards/>) to freeze their populations and definitions. Then inspect the join that first changes the counting unit.

<a id="summarize-tasks-before-joining-projects"></a>

## Summarize Tasks Before Joining Projects

For the proposed project-grain output, prepare one task summary per project identifier. PostgreSQL 18 documents grouping rows with `GROUP BY` in its [table-expression reference](<https://www.postgresql.org/docs/18/queries-table-expressions.html#QUERIES-GROUP>).

The following read-only query assumes tables containing exactly the fictional rows above. It does not connect to a system or create any records.

```sql
WITH task_summary AS (
  SELECT project_id,
         COUNT(*) AS task_count,
         SUM(planned_hours) AS planned_task_hours
  FROM tasks
  GROUP BY project_id
)
SELECT p.project_id,
       p.project_amount,
       s.task_count,
       s.planned_task_hours
FROM projects AS p
LEFT JOIN task_summary AS s
  ON s.project_id = p.project_id
ORDER BY p.project_id;
```

The expected output has three rows. P01 has amount one thousand, two tasks, and five planned hours. P02 has amount one thousand, three tasks, and seven planned hours. P03 has amount one thousand with null task-summary fields because no summary row matched.

The project amounts now sum to three thousand. The task summaries account for all five tasks and twelve planned hours. Keep those controls separate because [dollars, tasks, and hours](</articles/unit-conversion-business-reports/>) answer different questions.

For this complete synthetic export, a display layer may label P03 as having no exported tasks. Preserve the distinction between no matching summary and a measured value. If export [completeness](</articles/data-quality-dimensions-business-reports/>) is unconfirmed, leave the coverage question open.

If reviews also belong in the report, summarize them separately at project grain before joining. Do not join two detail exports and hope a final grouping will undo every repetition.

<a id="do-not-treat-distinct-as-a-universal-repair"></a>

## Do Not Treat DISTINCT as a Universal Repair

In this example, a distinct list of project amounts contains one value: one thousand. Adding that list produces one thousand, although three separate projects each carry that amount.

The two valid projects P01 and P02 also share the same amount. A distinct list containing project identifier and amount would preserve both, but an inner-join source would still omit P03. Choosing different columns changes what the distinct list represents.

Use the business identity and intended grain to decide which rows belong. Repeated values alone cannot identify redundant records.

Do not turn a join investigation into entity merging. [CRM data quality](</articles/crm-data-quality-automation/>) concerns the validity and consistency of business records. Here, the records can all be valid while their combination repeats a measure. Correct the report's relationship before considering any source-record change.

If a project key is unexpectedly repeated in the project export, stop the assumed one-row-per-project calculation. The file might contain revisions, locations, or snapshots. Ask the source owner what distinguishes those rows. Do not select an arbitrary survivor.

<a id="copy-the-join-review-worksheet"></a>

## Copy the Join Review Worksheet

Retain one worksheet for each proposed join. Add the export names, cutoff, reviewer, and query version above it. Keep sensitive source data in its authorized location.

| Check | Synthetic result | Entry for your report |
| --- | --- | --- |
| Left grain and key | One project; project ID | \[Meaning and complete key\] |
| Right grain and key | One task; task ID | \[Meaning and complete key\] |
| Join condition | Equal project IDs | \[All fields and conditions\] |
| Key uniqueness | Left unique on join key; right repeated | \[Counts and exception references\] |
| Unmatched left keys | P03 | \[Keys or controlled evidence\] |
| Unmatched right keys | None | \[Keys or controlled evidence\] |
| Expected output rows | Inner: 5; left: 6 | \[Calculation by key\] |
| Observed output rows | Inner: 5; left: 6 | \[Retained query result\] |
| Source project total | 3,000 dollars | \[Measure and source total\] |
| Raw joined project total | Inner: 5,000; left: 6,000 | \[Observed total and bridge\] |
| Selected output grain | One project after task summary | \[Approved grain and method\] |
| Reconciled output | 3 rows; 3,000 dollars | \[Rows, totals, residual, owner\] |

Treat an unexplained residual as unresolved. Matching the row count alone is insufficient: a wrong key can replace expected matches with unrelated matches. Inspect identifiers as well as totals.

Repeat the review when an export adds a new grain, such as project revisions or task assignments. [Record the changed assumption](</articles/decision-journal-business-operators/>) before accepting the next report. A previously correct relationship needs fresh evidence when its inputs change.

Keep the row meaning attached to the number.

<a id="sources"></a>

## Sources

- [PostgreSQL 18: Table Expressions](<https://www.postgresql.org/docs/18/queries-table-expressions.html>). Matching pairs, inner and outer joins, and grouping behavior. Checked 2026.10.28. Worksheets and synthetic arithmetic are editorial examples.
