2

I have a table like this

enter image description here

What I am trying to achieve is weighted rating. For e.g., in this table we have five star as 3, four star as 1, three star as 1, two star as 1 and one star as 0. So the weighted rating is

((5x3) + (4x1) + (3x1) + (2x1) + (1x0)) / (3+1+1+1) = 4

for service uuid ff0177fe. How can I achieve this ?

4

You just need an aggregative query using GROUP BY like so:

SELECT 
    service_uuid, 
    ((5 * SUM(five_rating)) + (4 * SUM(four_rating)) + (3 * SUM(three_rating)) + (2 * SUM(two_rating)) + SUM(one_rating))
    /
    (SUM(five_rating) + SUM(four_rating) +  SUM(three_rating)  + SUM(two_rating) + SUM(one_rating))
FROM RatingsTable
GROUP BY service_uuid

Also just keep in mind that your columns will return integer results and therefore the / symbol will do integer division which means it'll round down to the nearest 0 (e.g. 5 / 2 = 2). If you want a weighted rating that includes fractional decimal points then you just need to cast either the divisor or dividend to float or decimal. You can find more information about this in this StackOverflow post's accepted answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.