Mysql UNION and GROUP BY
18,101
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.
Author by
Admin
Updated on June 16, 2022Comments
-
Admin almost 2 years
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?