如何在一个表中显示多行，另一个表中的一行作为单行

Question

I have two tables. Table "user" contains user_id, user_name Table "Transactions" contains user_id, transactions

Eg:

TABLE USER:
user_id, user_name
1, Ram
2, John
3, Rahim

TABLE Transactions
user_id, transactions
1,  500
1, 300
2, 250
1, 450
3, 100
1, 250
3, 50

I want to display the result as:

user_name, Total
Ram, 1500*
John, 250
Rahim, 150

• it is the sum of all transactions of that particular user

How can I do this?

Answer 1

SELECT 
    u.user_name, 
    SUM(t.transactions) Total
FROM users u
LEFT JOIN transactions t ON t.user_id = u.user_id
GROUP BY t.user_id

I think you should use LEFT JOIN because it should select user's total (NULL) in any case, even if it's 0