Sum value from first table only once with join-CodePudding

I have two tables

Table 1 - "B2B - Quotas (DIRECT)" Q - Single row per day

| Date (dd/mm/yyyy) | Georgina Target| Date Lookup (unix timestamp)    |
| :---------------- | :--------------| :-------------------------------|
| 1/1/22            | $10            | unix_timestamp(date)            |
| 2/1/22            | $10            | unix_timestamp(date)            |
| 3/1/22            | $10            | unix_timestamp(date)            |

Table 2 - "B2B - Accounts to Invoices" A - Multiple rows per day

| Date (dd/mm/yyyy) | Total Directs  | Date Lookup (unix timestamp)    |
| :---------------- | :--------------| :-------------------------------|
| 1/1/22            | $5             | unix_timestamp(date)            |
| 1/1/22            | $9             | unix_timestamp(date)            |
| 1/1/22            | $8             | unix_timestamp(date)            |
| 3/1/22            | $2             | unix_timestamp(date)            |
| 3/1/22            | $3             | unix_timestamp(date)            |
| 3/1/22            | $7             | unix_timestamp(date)            |

What I want to return is:

| Date (dd/mm/yyyy) | Total Directs| Georgina Target   |
| :---------------- | :----------- | :---------------  |
| 1/1/22            | $22          | $10               |
| 2/1/22            | $0           | $10               |
| 3/1/22            | $12          | $10               |

I am currently using this SQL:

SELECT
         Q."Date" AS "Date",
         sum_if(A."Account Type"  REGEXP 'direct', A."Invoice Amount") AS "Total Directs",
         sum(Q."Georgina Target") AS "Georgina Target"
FROM "B2B - Quotas (DIRECT)" Q
LEFT OUTER JOIN "B2B - Accounts to Invoices" A ON A."Date Lookup"  = Q."Date Lookup"  
GROUP BY  "Date"

The issue is 'Georgina Target' is summing multiple times (based on the amount of rows in the table - "B2B - Accounts to Invoices" A for that date).

Note: "Total Directs" may not have a row for each date, but I need every date shown (based on the table "B2B - Quotas (DIRECT)")

CodePudding user response：

Subquery to SUM() the total_directs. LEFT JOIN and COALESCE() for your note. Assuming the QUOTAS table can't have multiple targets for a single date.

SELECT
    q.date
    ,q.target --No need to aggregate since Target has a single value per day
    ,COALESCE(a.total_directs,0) -- DBMS dependent  
FROM b2b_quotas AS q
LEFT JOIN (
    SELECT
        SUM(total_directs) as total_directs
        ,date
    FROM invoices
    GROUP BY date
) AS a ON q.date = a.date