Window Aggregations

Tyler shared this question ago

Answered

Hello,

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:

30440c135852107fa07f48db8b411ed9

Desired data set with window aggregation and rank over TeamID

4bbb5f031034372c71540d70af7cba90

The same question

Replies (1)

Guest ●

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.TeamId
       , 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...

regards,

David

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.TeamId
       , 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...

regards,

David

Reply URL