Window Aggregations

Tyler shared this question 4 years ago


I'm having trouble figuring out how to do window calculations over different groups within a column. I have data with a time component but what I am trying to do is sum sub groups within a column over the entire range of dates and then build a ranking upon those sums.

The only other way I think I could do this would be to build another report without the date column and do my rankings there and then link my time series report with my ranking report, but that seems like a lot of extra work. Below is a sample data set of what I have and the desired data set if I were able to do the windowing aggregations and rankings. Is this able to be done all in one report? Thanks for the help.

Sample data set:


Desired data set with window aggregation and rank over TeamID


Replies (1)


Hi Tyler,

I'm not a consultant, just a support guy, but the way I would go about this requirement would be by using freehand SQL report feature and then using code something like:

Select Top 12 
       , tc1.date_col
       , SUM(tc2.points) TeamPoints
       , rank() over (order by SUM(tc2.points) desc) [rank] 
from TeamComp tc1
inner join TeamComp tc2 on tc1.teamId = tc2.teamid
Group By tc1.TEamId, tc1.date_col
Order By TeamPoints DESC

hope that gets you on the way...



Leave a Comment
Attach a file