I have 2 tables I need to add together based on a date and 2 values.
This gives me the list of all information - fine.
$query = (SELECT date, debit, credit , note FROM proj3_cash )
UNION
(SELECT settle, purch, sale, issue FROM proj3_trades)
ORDER BY date";
Now I need to GROUP the information for daily totals from the two tables.
$query = "(SELECT date, SUM(debit), SUM(credit)FROM proj3_cash GROUP BY date)
UNION
(SELECT settle as date, SUM(purch) as debit, SUM(sale) as credit FROM proj3_trades GROUP BY date)
ORDER BY date";
Fine, but if there is something on the same date in each table I get this:
date SUM(debit) SUM(credit)
--------------------------------------
2010-12-02 0.00 170.02
2010-12-02 296449.91 233111.10
How do I group the two into the same day?
If I add GROUP BY at the end - I only get an error. Or should this be done with a JOIN?
You can achieve this using derived table:
SELECT date, SUM(debit), SUM(credit)
FROM
(
SELECT date, debit, credit
FROM proj3_cash
UNION ALL
SELECT settle as date,
purch as debit,
sale as credit
FROM proj3_trades
) derivedTable
GROUP BY date
ORDER BY date
I've changed UNION to UNION ALL because union will eliminate duplicates found in both tables.