Mysql UNION and GROUP BY

Question

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?

score 20 · Answer 1 · answered Sep 19 '12 at 08:08

20

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.

answered Sep 19 '12 at 08:08

Nikola Markovinović

18,963
5
46
51

hi, i have to create a view with latest record based on created date , Both the tables in union's have records 1 record each for same customerId but in the view developed from this join i need only the most recent one . i have similar view with GROUP BY customerid – Anoop P S Apr 28 '21 at 12:57

Mysql UNION and GROUP BY

1 Answers1