CODEWITHSUNDEEP
mysqlsql
Jataca
Jataca

Reputation: 13

Half of union subquery returning null

I want to break my money column up into two columns (type a and b)

select case when t.type = 'a' then t.amount end,  
       case when t.type = 'b' then t.amount end, 
       t.accountNumber
    from (
    select 
    sum(t1.money) as amount,
    'a' as type,
    t1.accountNumber as accountNumber
    from table1 t1
    group by t1.accountNumber
    union
    select 
    sum(t2.money) as amount,
    'b' as type,
    t2.accountNumber as accountNumber
    group by t2.accountNumber
    from table2 t2  
    )t;

However, right now type b is returning a bunch of 'null' values. It seems to depend solely on which table comes before the union. For example, if i put type b where type a is then I would see the type b values and the type a values would be null. How can I see both values?

(First time posting so I'd also appreciate pointers on how I could ask questions better) Thanks! :)

Upvotes: 0

Views: 85

Answers (1)

jpw
jpw

Reputation: 44871

From your comments I think that the query below might be what you want, it would give you an output like:

sum_a   sum_b   accountNumber
100,00  NULL    1            -- account 1 is in table1
200,00  300,00  2            -- account 2 is in both tables
NULL    300,00  3            -- account 3 is in table2



select 
    max(case when t.type = 'a' then t.amount end) sum_a,  
    max(case when t.type = 'b' then t.amount end) sum_b, 
    t.accountNumber
from (
    select 
       sum(t1.money) as amount,
       'a' as type,
       t1.accountNumber as accountNumber
    from table1 t1
    group by t1.accountNumber
    union all
    select 
       sum(t2.money) as amount,
       'b' as type,
       t2.accountNumber as accountNumber
    from table2 t2  
    group by t2.accountNumber
)t
group by t.accountNumber;

Alternatively you could use a full outer join to achieve the same result:

select 
    sum(a.money) sum_a, 
    sum(b.money) sum_b, 
    coalesce(a.accountNumber, b.accountnumber) AccountNumber
from table1 a full outer join table2 b on a.accountnumber = b.accountnumber
group by coalesce(a.accountnumber, b.accountnumber);

Upvotes: 1

Related Questions