JOINing two tables and make group and sums correct

Question

Been trying to figure this out for hours now and would really appreciate your help!

So I have two tables, transactions and transactions_rebuy

My SELECT goal is to join/combine these tables and get them grouped by date.

Right now I have this statement:

SELECT tr.SoldDate, SUM(tr.TotalAmount) as SumByReceipt,
COUNT(DISTINCT(tr.`ForsNr`)) as TotalCustomers, rebuy.RebuySum
FROM transactions tr   
CROSS JOIN (
SELECT SUM(TotalAmount) as RebuySum
    FROM transactions_rebuy
    WHERE SoldDate BETWEEN '2016-10-22' AND '2016-10-27'
    ) as rebuy
WHERE tr.SoldDate BETWEEN '2016-10-22' AND '2016-10-27' 
GROUP by tr.SoldDate
ORDER by tr.SoldDate DESC

Which gives me following result:

SaldDatum SumByReceipt TotalCustomers RebuySum

2016-10-27 54855.8001       99        10435.9997
2016-10-26 41749.8009       76        10435.9997
2016-10-25 46626.8001       79        10435.9997
2016-10-24 38678.8017       76        10435.9997
2016-10-22 30351.9997       48        10435.9997

The problem is that "RebuySum" column is grouped of the total sum for all the above dates. As "SumByReceipt", I want it grouped BY DATE, not the total sum on each row.

I've tried back and forth with grouping and sum, and got it to work but then it messes up the "SumByReceipt" column instead.

Most grateful for ideas on this.

Answer 1

Group the subquery by date, then join them on the date.

SELECT tr.SoldDate, SUM(tr.TotalAmount) as SumByReceipt,
        COUNT(DISTINCT(tr.`ForsNr`)) as TotalCustomers, IFNULL(rebuy.RebuySum, 0) RebuySum
FROM transactions tr   
LEFT JOIN (
    SELECT SoldDate, SUM(TotalAmount) as RebuySum
    FROM transactions_rebuy
    WHERE SoldDate BETWEEN '2016-10-22' AND '2016-10-27'
    GROUP BY SoldDate
    ) as rebuy
ON tr.SoldDate = rebuy.SoldDate
WHERE tr.SoldDate BETWEEN '2016-10-22' AND '2016-10-27' 
GROUP by tr.SoldDate
ORDER by tr.SoldDate DESC