SQL nested request

Question

I have 3 tabled:

1. transaction - general info about user balance change. primary key : transaction_id.
2. bet_transactions - special info about bet, foreign key : transaction_id.
3. win_transaction - same as bet_transactions, but contain wins.

I want to get sum of all bets and wins. I came up with this:

select
    u.username as username,
    u.balance as balance,
    sum(bt.amount) as bet_sum,
    sum(wt.amount) as win_sum
from
    users u,
    (
        select 
            t.*
        from 
            transactions t,
            bet_transactions b
        where 
            t.transaction_id = b.transaction_id
    ) bt,
    (
        select 
            t.*
        from 
            transactions t,
            win_transactions w
        where 
            t.transaction_id = w.transaction_id
    ) wt
where
    u.username = bt.username and
    u.username = wt.username
group by
    u.username

It doesn't work properly: I always get only one row and sums are not correct. But if I remove one of nested parts like this, it starts to work as expected. What am I doing wrong?

select
    u.username as username,
    u.balance as balance,
    sum(bt.amount) as bet_sum
from
    users u,
    (
        select 
            t.*
        from 
            transactions t,
            bet_transactions b
        where 
            t.transaction_id = b.transaction_id
    ) bt
where
    u.username = bt.username
group by
    u.username

Answer 1

Move aggregate functions and group by to nested sub-queries, something like this:

select
    u.username as username,
    u.balance as balance,
    bt.bet_sum,
    wt.win_sum
from
    users u
    left outer join 
    (
        select 
            t.username, sum(b.amount) as bet_sum
        from 
            transactions t,
            bet_transactions b
        where 
            t.transaction_id = b.transaction_id
        group by
            t.username
    ) bt
       on u.username = bt.username
    left outer join
    (
        select 
            t.username, sum(w.amount) as win_sum
        from 
            transactions t,
            win_transactions w
        where 
            t.transaction_id = w.transaction_id
        group by
            t.username
    ) wt
        on u.username = wt.username

More readable with CTE:

WITH 
bt as 
     (
        select 
            t.username, sum(b.amount) as bet_sum
        from 
            transactions t,
            bet_transactions b
        where 
            t.transaction_id = b.transaction_id
        group by
            t.username
    ),
wt as
    (
        select 
            t.username, sum(w.amount) as win_sum
        from 
            transactions t,
            win_transactions w
        where 
            t.transaction_id = w.transaction_id
        group by
            t.username
    ) 
select
    u.username as username,
    u.balance as balance,
    bt.bet_sum,
    wt.win_sum
from
    users u
    left outer join bt
       on u.username = bt.username
    left outer join wt
        on u.username = wt.username

EDITED:

I switched to proper ANSI join syntax, also to outer joins and CTE. Thanks @ThorstenKettner about suggestion.