Posted By: Anonymous
using a SQL query I am trying to get a max value from multiple rows, using 2 columns as ‘key’, and then sum them and move on t next ‘key’
Here is an example table. It has years, userid and points. Each year has several weeks.
What I want to do is to take each users MAX points for each year and SUM them.
I’d like the result for each year to be
User 1: 2020, 1, 12 2021, 1, 9 User 2: 2020, 2, 10 2021, 2, 13
…and after summing them, sorted by points:
…and so forth (adding on users and years)
Any help is very much appreciated.
Per Gordon’s helpful answer this is the query:
SELECT username, userdb.userid, SUM(points) as points FROM (SELECT standing.*, row_number() over (partition by standing.userid, year ORDER BY points desc) AS seqnum FROM standing) t JOIN userdb on userdb.userid = t.userid WHERE seqnum = 1 GROUP BY userid ORDER BY points DESC
You can use two levels of aggregation:
select userid, sum(max_points) from (select userid, year, max(points) as max_points from t group by userid, year ) uy group by userid;
Alternatively, you could handle this by filtering such as by using a window function:
select userid, sum(points) from (select t.*, row_number() over (partition by userid, year order by points desc) as seqnum from t ) t where seqnum = 1 group by userid;