count values between two ranges in mysql

Issue

I have a table something like this

fromrank torank prize poolid
1 1 4500 72
2 4 1500 72
5 6 250 72

now i want to calculate the sum of prize using mysql query
for eg 1st rank get 4500 and 2nd rank to 4th rank each will get 1500 and 5th and 6th rank user get 250 each so the sum of prize should be 9500 on the basis of poolid but when i tried to sum only prize i get 6250 but the actual sum is 9500

Solution

All you need is to multiply prize to the ranks difference + 1

select sum(prize * (torank - fromrank + 1))
  from test_table;

dbfiddle

Answered By – ekochergin

Answer Checked By – Marie Seifert (AngularFixing Admin)

Leave a Reply

Your email address will not be published.