How to calculate the difference between multiple rows in SQL

Users

 Id Name
---------------------
1   Robert Baratheon
2   Cercei Lanister
3   Jon snow

Transactions

Id  UserId   Date     Amount    NewBalance   NewTotal
---------------------------------------------------------
1   1      2020/03/21   100        100         100
2   1      2020/04/06   100        200         200
3   1      2020/04/07   25         225         225
4   2      2020/03/03   100        100         100
5   2      2020/04/07   30         130         130
6   3      2020/04/06   250        250        250

The newBalance is the sum of all amounts to this date and the newTotal is the sum of all positives amounts to this date.

I want to order who has made the most money this week

Result Desired :

UserId   UserName     Amount_made_this_week
---------------------------------------------
1   3      Jon snow           250
2   1      Robert Baratheon   125
3   2      Cercei Lanister    30

How do I write that in SQL?

Answer

You can sum up your amount filtering for this week and order the results:

SELECT Transactions.UserId, Users.Name, Sum(Transactions.Amount) as amount_made_this_week
FROM Transactions
    INNER JOIN Users
        ON Transactions.UserID = Users.ID
WHERE Transactions.Date BETWEEN #04/05/2020# AND #04/11/2020#
GROUP BY Userid, Name
ORDER BY amount_made_this_week DESC;

Advertisement

Answer