CODEWITHSUNDEEP
mysql
webbernaut
webbernaut

Reputation: 353

MySQL SUM minus SUM and join

I am trying to combine two queries together and not sure the approach. I know very little about joins and how to work them.

I have two tables.

users: 

id, 
email, 
userNumber

balances: 

id, 
userNumber, 
credit, 
debit (multiple entries in which I use SUM - SUM group by userNumber)

My separate queries which work fine are below but I need to combined them somehow:

SELECT SUM(credit) - SUM(debit) as 'balance' FROM balances GROUP BY userNumber

SELECT u.id, u.email, u.userNumber,
g.userNumber AS groupID,
g.debit AS debit,
g.credit AS credit
FROM user AS u
LEFT JOIN balances AS g ON u.userNumber = g.userNumber

I basically need to convert the first table to have userNumber and balance and then use that to join the other table. Second table would not need debit or credit that would be replace with balance.

Upvotes: 0

Views: 139

Answers (2)

Tien Nguyen Ngoc
Tien Nguyen Ngoc

Reputation: 1555

You can use this

SELECT u.id, u.email, u.userNumber,
g.userNumber AS groupID,
g.debit AS debit,
g.credit AS credit,
g.balance AS balance 
FROM user AS u
LEFT JOIN (SELECT userNumber, SUM(credit) - SUM(debit) as 'balance' FROM balances GROUP BY userNumber) AS g ON u.userNumber = g.userNumber

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Without knowing what exact format you want, I can suggest that you just LEFT JOIN your first aggregation subquery to the second one.

SELECT
    u.id,
    u.email,
    u.userNumber,
    g.userNumber AS groupID,
    COALESCE(g.debit, 0) AS debit,
    COALESCE(g.credit, 0) AS credit,
    COALESCE(t.balance, 0) AS balance
FROM user AS u
LEFT JOIN balances AS g
    ON u.userNumber = g.userNumber
LEFT JOIN
(
    SELECT userNumber, SUM(credit) - SUM(debit) AS balance
    FROM balances
    GROUP BY userNumber
) t
    ON u.userNumber = t.userNumber

Upvotes: 0

Related Questions