Welcome Guest, Not a member yet? Register   Sign In
MYSQL Join Union Group By - two tables advice
#4

[eluser]nevsie[/eluser]
okay update... and please excuse the different values for column names... but in the first code example i reproduced it to make my explanation clearer - this one i have not!!!

in the below the two individual SELECTS work correctly when being running as alone... however, when bringing them together using the UNION is screws with the SUM and GROUP BY messing up the figures...
***Note i used two LEFT joins and flipped the tables instead of a LEFT and RIGHT join.
Code:
SELECT sum(payments.amount), sum(expenses.gross), month(payments.pdate) AS themonth
        FROM payments
        LEFT JOIN expenses ON month(expenses.expdate) = month(payments.pdate)
        GROUP BY month(payments.pdate)
        
        UNION        
        
        SELECT sum(payments.amount), sum(expenses.gross), month(expenses.expdate) AS themonth
        FROM expenses
        LEFT JOIN payments ON month(payments.pdate) = month(expenses.expdate)
        GROUP BY month(expenses.expdate)


Messages In This Thread
MYSQL Join Union Group By - two tables advice - by El Forum - 02-17-2010, 12:52 PM
MYSQL Join Union Group By - two tables advice - by El Forum - 02-17-2010, 01:05 PM
MYSQL Join Union Group By - two tables advice - by El Forum - 02-17-2010, 01:21 PM
MYSQL Join Union Group By - two tables advice - by El Forum - 02-17-2010, 01:45 PM
MYSQL Join Union Group By - two tables advice - by El Forum - 02-17-2010, 02:25 PM
MYSQL Join Union Group By - two tables advice - by El Forum - 02-17-2010, 02:35 PM
MYSQL Join Union Group By - two tables advice - by El Forum - 02-17-2010, 03:08 PM
MYSQL Join Union Group By - two tables advice - by El Forum - 02-17-2010, 03:19 PM



Theme © iAndrew 2016 - Forum software by © MyBB