How to create a query by renter to summarize multiple rent transactions within a given month

Question

Goal: To show all property units by property rather vacant or not with renter info and summary of all payments (by each rent, late, utilities) made in a given month. eg if renter makes partial payments Do not want to show each partial but summarize into one record.

Here is as close as I can get. The challenge is if the renter makes multiple payments in one month the query returns each payment as a separate record and will not summarize all the payments into one record.

=======================================================

SELECT
ROW_NUMBER() OVER(ORDER BY r.RenterID) AS rownum,
p.PropertyID,
pu.PropertyUnitID,
r.RenterID,
p.PropertyName,
pu.PropertyUnitName,
r.RenterLastName,
SUM(rpay.RentAmountPaid) OVER(PARTITION BY rpay.RenterID) AS RentAmountPaid,
rpay.LastModifiedDate AS LMD
FROM
tblProperty p FULL OUTER JOIN
tblPropertyUnits pu ON p.PropertyID = pu.PropertyID FULL OUTER JOIN
tblRenter r ON pu.PropertyUnitID = r.PropertyUnitID FULL OUTER JOIN
tblRentPayments rpay ON r.RenterID = rpay.RenterID
WHERE
pu.PropertyID = 3 AND MONTH(rpay.LastModifiedDate) = 2
ORDER BY
p.PropertyID, pu.PropertyUnitID

========================================

Answer 1

You want to do the following

Select blah, sum(paymentAmount)
from Renter
join properties
join payments
group by RenterId,PropertyId
    where dateRange = x

Answer 2

I can't read this, but you will likely have to group by renter and month and use aggregate function (e.g. sum()) to get monthly totals. If you need other columns in the result set that you cannot group by, you can nest the query and join the result set with the monthly data back to other tables that have these additional columns.