How to calculate a value for all rows in sql server? I have two tables. One is a linking table to handle a many-to-many relationship between users and construction projects, and the other one is a payment table which includes the users' payments. Now I want to calculate each user's payment to show it along with user's information. 
To do this, I wrote this query.

SELECT MemberID,
MembershipDate,
(SELECT SUM(Amount) from Payment where MemberID = 1001) AS Payment
FROM   User_Project_LinkingTable where MemberID = 1001


Now, how can I do this for all users to show all of them in a list?

Thanks in advance
This query solved my problem.

SELECT DISTINCT 
  UPL.memberid, 
  UPL.membershipdate, 
  PaymentSum.sumamount 
FROM user_project_linkingtable AS UPL 
LEFT JOIN (SELECT 
            memberid, 
            Sum(amount) AS SumAmount 
            FROM payment 
            GROUP BY memberid) AS PaymentSum 
 ON UPL.memberid = PaymentSum.memberid;