John Smith John Smith - 7 months ago 13
SQL Question

SUM AND GROUP BY WITH LIMITED ROWS

I would like to sum only the last 2 weeks for each user and GROUP BY User.

MY TABLE:


+----+------+--------+------+
| ID | User | Income | Week |
+----+------+--------+------+
| 1 | John | 50 | 1 |
+----+------+--------+------+
| 2 | John | 20 | 2 |
+----+------+--------+------+
| 3 | John | 25 | 3 |
+----+------+--------+------+
| 4 | John | 10 | 4 |
+----+------+--------+------+
| 5 | Mike | 45 | 1 |
+----+------+--------+------+
| 6 | Mike | 15 | 2 |
+----+------+--------+------+
| 7 | Mike | 10 | 3 |
+----+------+--------+------+
| 8 | Mike | 5 | 4 |
+----+------+--------+------+


DESIRED RESULT:


+------+--------+
| User | Income |
+------+--------+
| John | 35 |
+------+--------+
| Mike | 15 |
+------+--------+


As you can see, I'm summarizing week 4 and 3 for each user because those are the last 2 weeks.

Thanks in advance.

Answer

You could use a row_number() to sort out the top two weeks for a given user. Thereafter, you could aggregate. Works in postgresql.

Select user, sum(income) 
from(
     select user, income, row_number() over (partition by user order by week desc) rn 
     from your_table
    ) 
where rn<3 group by user;