CODEWITHSUNDEEP
mysqlsqlsuminner-join
Schalk
Schalk

Reputation: 17

Inner Join MySQL tables with sum of table

I searched the forum and tried a couple of the results on the form but I am still struggling with the following. Will you guys please give me advice on the following:

Table 1: transactions
Rows: user_id, amount, received

Table 2: wallet
Rows: user_id, wallet_id

I have 2 results which I need to join:

Result 1:

SELECT user_id, SUM(amount)FROM transactions WHERE received = '100' GROUP BY user_id

Result 2:

SELECT * FROM transactions INNER JOIN wallet ON transactions.user_id = wallet.user_id

What I am trying to do is to link the wallet_id of Table 2 by using the user_id and link it to the user_id on all transactions in table 1 which = to 100. I also need to sum the results on the amount row. So it will display the total of each user_id and link the wallet to it.

I have tried:

SELECT user_id, SUM(amount)FROM transactions
INNER JOIN wallet ON transactions.user_id = wallet.user_id
WHERE received = '100' GROUP BY user_id

but its not working. Please help guys ;-)

Upvotes: 0

Views: 235

Answers (4)

Schalk
Schalk

Reputation: 17

Thanks Guys. You guys rock!!!!! This is what worked for me:

SELECT wallet_id, received, SUM(amount)FROM transaction INNER JOIN wallet ON transaction.user_id = wallet.uid WHERE received = '100' GROUP BY wallet_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you explained why the code is failing, it would be easier to fix. At least one error is that user_id is undefined.

A simple solution is to use USING:

SELECT user_id, SUM(t.amount)
FROM transactions t INNER JOIN
     wallet w
     USING (user_id)
WHERE t.received = '100'
GROUP BY user_id;

Note: if received is a number, then do not enclose the 100 in single quotes.

Alternative, you can write the query by qualifying the column names -- and you should qualify all of them:

SELECT w.user_id, SUM(t.amount)
FROM transactions t INNER JOIN
     wallet w
     ON w.user_id = t.user_id
WHERE t.received = '100'
GROUP BY w.user_id;

If you want the wallet_id as well, then include it in the SELECT and GROUP BY:

SELECT w.user_id, w.wallet_id, SUM(t.amount)
FROM transactions t INNER JOIN
     wallet w
     ON w.user_id = t.user_id
WHERE t.received = '100'
GROUP BY w.user_id, w.wallet_id;

Upvotes: 0

nish
nish

Reputation: 1221

Hope this helps.

SELECT user_id, wallet_id, SUM(amount)FROM transactions t
JOIN wallet w ON t.user_id = w.user_id
WHERE received = '100' GROUP BY user_id, wallet_id;

Upvotes: 2

i3lai3la
i3lai3la

Reputation: 980

Just add group by user_id, wallet_id

SELECT user_id, wallet_id, SUM(amount)FROM transactions
INNER JOIN wallet ON transactions.user_id = wallet.user_id
WHERE received = '100' GROUP BY user_id, wallet_id

Upvotes: 0

Related Questions