Tronik Tronik - 1 month ago 6
MySQL Question

JOINing two tables and make group and sums correct

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

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
Comments