Menu
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?
964
The MySQL UNION Operator. The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns; The columns must also have similar data types; The columns in every SELECT statement must also be in the same order; UNION Syntax
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
MySQL UNION and ORDER BY If you want to sort the result set of a union, you use an ORDER BY clause in the last SELECT statement as shown in the following example: SELECT concat (firstName, ' ',lastName) fullname FROM employees UNION SELECT concat (contactFirstName, ' ',contactLastName) FROM customers ORDER BY fullname;
Here's the plan we get with a mere UNION of two GROUP BY queries: ? We see that there is a filesort in each of the queries. MySQL does allow using ORDER BY in the queries merged with UNION or UNION ALL. To do this, we just need to wrap each query into a set of parentheses: ? However, the plan remained the same. Why?
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.
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
