Math: Add total of scores, but omit a high and a low

Wappler General Need Help
1

We have a need to be able to do the following:

Have a set of judges (ranging from 5-7).
Each judge will be judging a category that contestants are competing in.
Each judge will give a contestant a score ranging from 1-12.
The tabulation of the scores is the following:

  • Omit a high score, if there are multiple scores that are all the highest score, only omit one of them.
  • Omit a low score, if there are multiple scores that are all the lowest score, only omit one of them.
  • Add the remaining scores together.

Each contestants scores would be added to the database, so this would be to us, simple query manipulation, but it might be on the client app side as well.

Any guidance would greatly be appreciated.

2

Hi.

You mean if there are two 12 scores, you want to remove one for calculations or not insert in DB? And if there are 4, then 3… 6 then 5 and so on?

3

Not exactly, we would want all the scores inserted into the database, but when we run a query to do the tabulation of the scores, only use the criteria of omitting two of the scores and adding the rest.

4

Ok. So save all in DB.
But for calculation, if 5 of 7 judges have given 12, just use the score of other two judges and ignore the 5 - is that the requirement?

5

So for example:
Judges would be left to right
Contestants would be top to bottom

# 1 2 3 4 5 6 7
1 4 6 10 10 9 10 10
2 7 7 9 8 7 9 7
3 10 5 10 9 7 11 11

In this example, Contestant #1 would receive the scores of 4,6,10,10,9,10,10.
We would ignore the 4 and one of the 10s and only add 6, 10, 10, 9, 10 so the total would be 45.

Contestant #2 would receive the scores of 7, 7, 9, 8, 7, 9, 7.
We would ignore one of the 7s and one of the 9s and add 7, 8, 7, 9, 7 so the total would be 38.

6

Thought I’d chip in quickly in the hope it’s helpful.

Something like:

select users.userid, sum(score) - max(score) - min(score) as totalScore from users join scores on users.userid=scores.userid group by users.userid

Haven’t tested it though.

7

This looks like a task for RunJS, but I am still unable to grasp the logic.
You are trying to remove one score for each score which are duplicates. But not for the lower value?

You can play around with the logic in JS with the data returned from DB, and use RunJS to get the desired output to be sent to client side.

8

We’ll give both suggestions a go.

@sitestreet’s suggestion is similar to how it appears in the excel spreadsheet,

=IF(T6="","",IF(COUNT(U6:AA6)=7,SUM(U6:AA6)-MIN(U6:AA6)-MAX(U6:AA6),SUM(U6:AA6)))

We have not looked into @sid’s suggestion of using RunJS, so it will be something new to examine. :slight_smile:

1 Like
9

So if the excel formula matches what Jon has suggested (which looks like so), the logic you have shared is quite off. Or it could be just me. :sweat_smile:
You can skip RunJS for this I think.

10

@sid perhaps one or both of us needs coffee! :slight_smile: :coffee: :coffee:

1 Like
11

You may need to tweak the query a bit to account for the number of judges:

select users.userid, if(count(score)=7,sum(scores) - max(scores) - min(scores),sum(scores)) as totalScore from users join scores on users.userid=scores.userid group by users.userid

1 Like