MySQL sum where inner join is missing from right or left table

Question

I have a turnover table on the one side that has :

Storeid Turnover myDate
 | 1   | 1000   | 2020-01-01 |
 | 1   |  200   | 2020-01-02 |
 | 1   | 4000   | 2020-01-03 |
 | 1   | 1000   | 2020-01-05 |

on the other side I have a table with the number of transactions:

Storeid Transactions myDate
 | 1   | 20          | 2020-01-01 |
 | 1   | 40          | 2020-01-03 |
 | 1   | 20          | 2020-01-04 |
 | 1   | 60          | 2020-01-05 |

I need to work out the sum of the turnover and the sum of the transactions for a given date range. However I might have missing dates on either one of the tables. If I sum them individually I get the correct answer for each but any sort of inner or left join and I get incomplete answers (as per below):

select sum(Turnover), sum(transactions) from TurnoverTable
left join TransactionTable on TurnoverTable.storeid = TransactionTable.storeid and
TurnoverTable.myDate = TransactionTable.myDate where TurnoverTable.myDate >= '2020-01-01'

This will produce a sum for Turnover of 6200 and for Transactions of 120 (20 is missing from the 2020-01-04 date as this date is not available in the Turnover table, therefore fails in the join).

Short of running 2 select sum queries, is there a way to run these sums?

Much appreciated.

score 1 · Answer 1 · answered Aug 20 '20 at 23:20

You have dates missing in both tables, which rules out a left join solution. Conceptually, you want to full join. In MySQL, where this syntax is not supported, you can use union all; the rest is just aggregation:

select sum(turnover) turnover, sum(transactions) transactions
from (
    select mydate, turnover, 0 transactions
    union all
    select mydate, 0, transactions
) t
where mydate >= '2020-01-01'

score 0 · Accepted Answer · answered Aug 20 '20 at 23:31

Regarding this kind of statistics, you should not use JOIN. Because you may get wrong results by rows duplications. Especially, we need to join many tables in practice.

So I recommend using UNION like the following: Please include a date where clause in UNION.

SELECT 
    Storeid,
    SUM(Turnover),
    SUM(Transactions) 
FROM
    (SELECT 
        Storeid,
        myDate,
        Turnover,
        0 AS Transactions 
    FROM
        turnovers 
    WHERE myDate BETWEEN '2020-01-01' 
        AND '2020-08-21' 
    UNION
    ALL 
    SELECT 
        Storeid,
        myDate,
        0 AS Turnover,
        Transactions 
    WHERE myDate BETWEEN '2020-01-01' 
        AND '2020-08-21' 
    FROM
        Transactions) AS t 
GROUP BY Storeid ;

MySQL sum where inner join is missing from right or left table

2 Answers2