21
Views
8
Solved
###### Aggregate - Get the sum of 1st N highest value
Question

Hi,

I have an aggregate with 12 Rows (filtered by some ID).

I need the sum of 1st 6 highest value.

With the above-mentioned information, expecting to have a summation of 145,000.00.

This can be done via SQL Statement but is there a way I can achieve this via Aggregate?

Thanks..

Elmer

Solution

Hi Elmer,

you can use some logic in OnAfterFetch Action instead of using sql and sum inside the aggregate

1. Sort your price in descending

3. Assign the variable inside the for each.

Hope this helps!

Regards,

Ellakkiya.S

Thanks Ellakkiya S,

Will try your suggestion. For the time-being, a simple SQL statement made the trick.

Thanks Marcio and Dorine,

Hi Elmer,

Though you already have the 2 possible solutions for your issue, let me add that the SQL statement (from my point of view) can be simplified to:

SELECT SUM(t0.Amount)

FROM (

SELECT TOP 6 Amount

FROM Table0

WHERE TransactionId = 123

ORDER BY Amount DESC

) t0

Easier to read and taking a simpler execution plan - though both should have the same executing cost.

Thanks Joao for taking time to answer this post. Indeed, this script helps though I (and my team) opted for the client action to generate the resultset.

Cheers.

Hi Elmer,

if you are sure that you always need the top6, you can set the agreggate Max Records to 6 and create a sum for Amount column.

Regards

Jose,

that's not going to work, the 6 refers to the number of returned lines, not the records before aggregation function.

So yes to the 6 max records, but sum would have to be calculated post aggregate.

Dorine

So Elmer, I would prefer to use SQL for this

Champion

I don't understand the downvote, but let me add one more thing, to get the highest values you also need to sort the data by the amount, descending.

Like Dorine said, after fetch aggregate, you need to do a for each and set the max iteration to the amount of numbers(records) you want to sum. And there you go, you have the sum of the 6 top records.

Kind Regards,

Márcio

Hi Jose,

Thanks but I don't think that setting to max 6 will work. The resultset will fetch the 6 after the sum and not before the sum.

Solution

Hi Elmer,

you can use some logic in OnAfterFetch Action instead of using sql and sum inside the aggregate

1. Sort your price in descending

3. Assign the variable inside the for each.

Hope this helps!

Regards,

Ellakkiya.S

Thanks Ellakkiya S,

Will try your suggestion. For the time-being, a simple SQL statement made the trick.

Thanks Marcio and Dorine,

Hi Elmer,

Though you already have the 2 possible solutions for your issue, let me add that the SQL statement (from my point of view) can be simplified to:

SELECT SUM(t0.Amount)

FROM (

SELECT TOP 6 Amount

FROM Table0

WHERE TransactionId = 123

ORDER BY Amount DESC

) t0

Easier to read and taking a simpler execution plan - though both should have the same executing cost.

Thanks Joao for taking time to answer this post. Indeed, this script helps though I (and my team) opted for the client action to generate the resultset.

Cheers.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.