I am making an app that you can vote things, I have a table that contains "rating" that goes from 0 to 100 and that contains "votes" too, which represents the number of votes. I am using:
UPDATE maincategory SET rating='75'/votes
But when nobody else have voted yet, then the votes are obviously "0". And this makes "75/0" (Division with "0"). How can I make an "If" statement or something else that verifies if that thing have "0" votes yet and not divide with "votes" (Because they're "0")?
Thanks in advance
EDIT: My vote logic doesn't make much sense, I have seen this now... Sorry, what must I make to correct this? Maybe add all ratings together and divide with the number of votes?
Just run it on the rows that have votes.
UPDATE maincategory SET rating='75'/votes WHERE votes>0
Leave the others at a default of 0 or NULL rating and handle that in your app.
Also, the '75'/votes bit sounds a bit off to me. With one vote, it'll have a rating of 75. With two votes, it'll have a rating of 37.5. Votes typically wouldn't decrease a rating.
Typically, the new rating would be set like so (this is pseudocode, not valid SQL):
rating = ((current_rating * current_vote_count) + new_rating) / (current_vote_count + 1)