Search code examples
mysqlsumrange

count values between two ranges in mysql

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