[RESOLVED] Arithmetic Averages

saint4

If I have a review table;

id rating
1 P
2 P
3 N
4 D
5 P

P = Positive, N = Negative and D = Neutral

How do I get the average number of reviews affected by positive and negative review ratings.

Formatted to display as 3/10 or 10/10.

laserlight

I am not sure what kind of average you are talking about here. Could you elaborate on how you intend to make this computation, and provide an example?

saint4

I didn't quite understand it myself lol.

Previously I used a rating system where a user would submit a rating out of 10, 1 being very poor and 10 being excellent.

The number would go into the database and I would use SELECT AVG(rating) as rating etc... to get the average rating from the database rounding if necessary.

Now I would like to make it simpler by a user selecting postive review, negative or opt to be neutral.

In this case I would still like to be able to represent the answer as an average out of 10.

leatherback

I think the problem is then not as much a php/coding issue, as it is a problem of defining how to get from one scale to the other.

What you could do is say that neg = 1, opt = 4 / 5 and pos =10, multiple the count of each option with the value, sum that, and divide by the number of replies?

laserlight

What you could do is say that neg = 1, opt = 4 / 5 and pos =10, multiple the count of each option with the value, sum that, and divide by the number of replies?

That would be an option, though if the negative value is 1 and the positive value is 10, then to be fair the neutral value should be 5.5.

leatherback

You're right;

saint4

Hi there.

What about counting the rows where rating = P (ex: 10)
Count the rows where rating != D ( Where it doesn't equal neutral, but includes negative values and positive ) for example we will say there is 12 altogether. 2 being negative.

(10/12)*10 = round (A) gives us 8/10

laserlight

What about counting the rows where rating = P (ex: 10)
Count the rows where rating != D ( Where it doesn't equal neutral, but includes negative values and positive ) for example we will say there is 12 altogether. 2 being negative.

(10/12)*10 = round (A) gives us 8/10

The mechanism is entirely up to you, of course. Your proposed mechanism basically calculates the percentage of positive votes out of the non-neutral votes, to the nearest 10%. You would need to consider the case where all the votes are neutral. Also, consider if it is fair if there are 99 neutral votes and 1 positive vote, yet the score is 10 out of 10.

saint4

I was thinking if it was neutral then it is neither positive nor negative so therefore neutral ratings don't count at all.

Alternatively it could just be left as positive and negative ratings and drop neutral ratings entirely.

laserlight

Alternatively it could just be left as positive and negative ratings and drop neutral ratings entirely.

Yes, that may be better to simplify everything, from your code to your users' need to decide.

saint4

Thanks.

leatherback

Hi Saint,

I THINK that would toss your averages big time. A mid-rating is not the same as not being reated. Why are you not including all the ratings?
a simple:
select count(id) from table group by rating
would give you the count by rating. Then multiply, sum and devide the numebrs, should be about 6 lines of code to get a more realistic rating. You just need to decide on the weightings..
😃