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

[eluser]nevsie[/eluser]
A brief synopsis...
I have two tables income and expenses. Both have many columns, but the ones i am interested in are date and amount... I want to output monthly total amounts for all entries in income and expenses.

Individually i can run something like:

Code:
SELECT sum(income.amount), sum(expense.amount), month(payments.incdate)
  FROM income
  JOIN expense ON month(expense.expdate) = month(income.incdate)
  GROUP BY month(income.incdate)

Now in the example above this works fairly well but as it is a JOIN it only returns results where income and expense both have entries for that month. If i do a LEFT JOIN it brings back the extra rows from income table, and visa versa RIGHT JOIN extra rows from expense table - ideally FULL JOIN would bring back both sides, but FULL JOIN is not supported by MYSQL...

Therefore i tried doing two SELECT's like the above one for LEFT and one for RIGHT and then UNION to bring them together. But union caused no end of problems for me! Mostly messing up and mixing up the two sum(value) amounts and mixing the adding etc.

I have also tried UNIONing two simpler queries and place a NULL value in the select hoping the union would track the results columns, etc, but this would not bring the results together it just created more rows in the result.

Yes i could do this with multiple queries, and run some for/foreach loops to join the results, but i was just trying to be cleaner and cleverer and do it properly (learn at the same time).

Any ideas appreciated - especially if i am overcomplicating this! Thanks, N
#2

[eluser]danmontgomery[/eluser]
I'm interested in this... Possible for you to dump some test data? I'm thinking you could use IFNULL() with left/right join
#3

[eluser]nevsie[/eluser]
sample data? do you mean some working content like sql for income and expense so you can play? if so i will produce a basic overview of what i have and get back to you...

in regards to ifnull - this is not really the issue (unless i have misinterpreted) as when doing a left join and union right join - the sum of the groups get confused and display some crazy totals... my next idea into this is to make more use of brackets to seperate the two select queries before doing the union to keep the two sums apart - but this is just wild guessing in my head!
#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)
#5

[eluser]danmontgomery[/eluser]
[quote author="nevsie" date="1266456115"]sample data? do you mean some working content like sql for income and expense so you can play? if so i will produce a basic overview of what i have and get back to you...[/quote]

Yeah, just a mysql dump of the two tables, or a few rows of the tables
#6

[eluser]nevsie[/eluser]
sent a pm with some example data for you as sql.
your help is appreciated.

my latest idea is to sub set a select query within a from - for example

Code:
SELECT value1, value2 FROM ( SELECT...... )......
#7

[eluser]danmontgomery[/eluser]
Code:
SELECT IFNULL( t_payments.total, 0 ) AS payments, IFNULL( t_expenses.total, 0 ) AS expenses, t_payments.month
FROM (
  SELECT SUM( payments.amount ) AS total, month( payments.pdate ) AS `month` FROM payments
  GROUP BY month( payments.pdate )
) AS t_payments
LEFT JOIN (
  SELECT SUM( expenses.gross ) AS total, month( expenses.expdate ) AS `month` FROM expenses
  GROUP BY month( expenses.expdate )
) AS t_expenses ON t_expenses.month = t_payments.month

That's gonna depend on their being payments in every month, I guess
#8

[eluser]nevsie[/eluser]
yes that is the direction i am heading in, but exactly right - this only flies if the LEFT part of the JOIN has a value in each month. Yes i expect there to be, but i cannot guarantee that!!! i got similar results using a different query but you cannot rely on a single sided join! Oh why not bring in FULL JOIN!!!!




Theme © iAndrew 2016 - Forum software by © MyBB