Stop double-counting your CRM and billing data.

Try a free interactive SQL join example. Change the number of deals and invoices, see duplicated totals, and download the corrected query for your AI agent.

By the Combined team · September 10, 2026

One company. Change its sample records.Open CRM deals$10,000 per dealMatching paid invoices$2,500 per invoice

2 deals + 2 paid invoices → 4 rows after the join

Sum the raw join

Open pipeline: $40,000
Paid invoices: $10,000

Repeated rows inflate the totals.

Aggregate, then join

Open pipeline: $20,000
Paid invoices: $5,000

Each original record contributes once.

Each deal appears 2 times; each invoice appears 2 times. Group each source by company and currency before combining the totals.

Inspect the 4 joined rows

Every match in the raw left join, in USD
Deal	Pipeline	Invoice	Paid
deal_1	$10,000	invoice_1	$2,500
deal_1	$10,000	invoice_2	$2,500
deal_2	$10,000	invoice_1	$2,500
deal_2	$10,000	invoice_2	$2,500

Download this sample's SQL

Give your AI agent this workflow with your own data. Explore Combined →

Two deals and two invoices can become four joined rows. If your agent sums those rows, it can double both pipeline and invoice totals. Change the record counts to see why, then download the SQL that aggregates each source before joining.

What the sample shows

This sample company has open CRM deals worth $10,000 each and paid invoices worth $2,500 each. Every record belongs to the same company and uses USD. The browser calculates the example as you change the counts; the download contains a matching, runnable DuckDB query.

Download the default two-deal, two-invoice example. It returns $40,000 and $10,000 for the raw join, compared with $20,000 and $5,000 after separate aggregation. These are synthetic teaching records, so you can run the file without connecting an account.

A matching key does not make a row unique

A company identifier tells the query which records belong together. It does not reduce that company's deals or invoices to one row. When two deals match two invoices, each deal is repeated for both invoices, and each invoice is repeated for both deals. This is often called join fanout.

Open the row inspector above to see every pair. With three deals and four invoices, the join produces twelve rows. Pipeline appears four times, while paid invoices appear three times. A one-to-one test can conceal the mistake because neither side is repeated. The behavior follows the matching rules described in DuckDB's join documentation.

SUM(DISTINCT amount) does not repair the relationship. This sample deliberately gives different deals the same amount. Deduplicating their dollar values would erase legitimate deals. Keep record identity separate from record value.

Give each source the same reporting grain

Decide what one result row represents. Here it is one company in one currency. Sum the open deals at that level, sum qualifying invoices at the same level, then join those two small result sets. Each source contributes one row to the final match.

WITH pipeline AS (
  SELECT company_id, currency, SUM(amount) AS pipeline
  FROM deals GROUP BY company_id, currency
), billing AS (
  SELECT company_id, currency, SUM(amount_paid) AS paid
  FROM invoices GROUP BY company_id, currency
)
SELECT p.company_id, p.currency, p.pipeline, b.paid
FROM pipeline p
LEFT JOIN billing b USING (company_id, currency);

The fragment assumes already filtered, normalized teaching tables. The downloadable sample includes them. In a real query, choose the deal stages, invoice states and time boundaries first. Keep currencies separate until you have an explicit conversion policy. The lab uses a known complete sample and displays zero for an empty invoice set; missing real-world coverage can instead mean the answer is unknown.

Aggregates reduce records to the requested grouping. Their treatment of missing values is described in DuckDB's aggregate reference. Paid invoice amounts also answer a different business question from recurring or recognized revenue.

Give your agent the question and the checks

Combined gives your agent a managed, synced data layer across your business apps. Connect the CRM and billing sources once, grant the relevant datasets, and ask for an answer backed by the actual records. Use this instruction as a starting point:

Use my Combined MCP connection.
Discover the granted CRM and billing datasets and inspect their schemas.
Show the company-to-billing-customer mapping and check its uniqueness.
Agree the open-deal stages, paid-invoice interval and currency rules with me.
Aggregate each source by company and currency BEFORE joining.
Keep companies with open deals when no qualifying invoice matches.
Compare each metric against its independent source total.
Return the result, source freshness, unmatched mappings and query receipt.
If the required fields or coverage are missing, identify the missing input.

Start with the HubSpot and Stripe connector guides, or use your own CRM and billing combination. The full HubSpot–Stripe tutorial adds an explicit customer map, invoice payment dates and multiple currencies. The free agent kit makes that workflow reusable.

Sources and further reading

Explore the documentation behind this guide. Product details checked on September 10, 2026.