Aggregate Sum from top 4 ??

[jimmyWhitetrix]

Hello,

In the attached image, you can see that I have a student with 6 subject results. I trying to create a calculated column that will produce the Sum() of this student's top 4 subjects.

I am using JasperSoft Reports Server. The only function that seems of some use is Rank(). Typically I would use something like: LARGE(range,n), but I appreciate this is not an option.

Any help appreciated!

 

Many thanks,

[jgust]

From a SQL perspective here is how you can do it using ROW_NUMBER.  I added RANK and DENSE_RANK so that you can determine how you would like to handle duplicate values.  I added a test duplicate value as *TST*.

select STUDENT, SUM(SCORE) AS TOP_4_SCOREFROM (    SELECT Student         , class_cd         , SCORE         , ROW_NUMBER() OVER (PARTITION BY student order by score desc) as rn         , RANK() OVER (PARTITION BY student order by score desc) as rnk         , DENSE_RANK() OVER (PARTITION BY student order by score desc) as drnk    FROM (             SELECT 'Charis' as Student, 'LIT' AS class_cd, 74 AS score             UNION ALL             SELECT 'Charis' as Student, 'REL', 82             UNION ALL             SELECT 'Charis' as Student, 'MAS', 85             UNION ALL             SELECT 'Charis' as Student, 'CHE', 87             UNION ALL             SELECT 'Charis' as Student, '*TST*', 87             UNION ALL             SELECT 'Charis' as Student, 'PHY', 88             UNION ALL             SELECT 'Charis' as Student, 'MAM', 91         ) A    ) BWHERE RN <= 4GROUP BY STUDENT;[/code]

 

[jimmyWhitetrix]

Hi jgust,

A huge thank you for your assistance. I have been investigating how I can apply an SQL query to my dataset. I have now switched from JasperSoft Reports Server into jaspersoft studio.

Thanks again, the query has been very helpful.