CODEWITHSUNDEEP
mysqlsql
Terry Uhlang
Terry Uhlang

Reputation: 141

Compare rows and get percentage

I found it hard to find a fitting title. For simplicity let's say I have the following table:

cook_id cook_rating
1       2
1       1
1       3
1       4
1       2
1       2
1       1
1       3
1       5
1       4
2       5
2       2

Now I would like to get an output of 'good' cooks. A good cook is someone who has a rating of at least 70% of 1, 2 or 3, but not 4 or 5.

So in my example table, the cook with id 1 has a total of 10 ratings, 7 of which have type 1, 2 and 3. Only three have type 4 or 5. Therefore the cook with id 1 would be a 'good' cook, and the output should be the cook's id with the number of good ratings.

cook_id cook_rating
1       7

The cook with id 2, however, doesn't satisfy my condition, therefore should not be listed at all.

select cook_id, count(cook_rating) - sum(case when cook_rating = 4 OR cook_rating = 5 then 1 else 0 end) as numberOfGoodRatings from cook
where cook_rating in (1,2,3,4,5)
group by cook_id
order by numberOfGoodRatings desc

However, this doesn't take into account the fact that there might be more 4 or 5 than good ratings, resulting in negative outputs. Plus, the requirement of at least 70% is not included.

Upvotes: 0

Views: 695

Answers (2)

Chris Trahey
Chris Trahey

Reputation: 18290

You can get this with a comparison in your HAVING clause. If you must have just the two columns in the result set, this can be wrapped as a sub-select select cook_id, positive_ratings FROM (...)

SELECT 
  cook_id, 
  count(cook_rating < 4 OR cook_rating IS NULL) as positive_ratings, 
  count(*) as total_ratings
FROM cook
GROUP BY cook_id
HAVING (positive_ratings / total_ratings) >= 0.70
ORDER BY positive_ratings DESC

Edit Note that count(cook_rating < 4) is intended to only count rows where the rating is less than 4. The MySQL documentation says that count will only count non-null rows. I haven't tested this to see if it equates FALSE with NULL but I would be surprised it it doesn't. Worst case scenario we would need to wrap that in an IF(cook_rating < 4, 1,NULL).

Upvotes: 3

Denys S&#233;guret
Denys S&#233;guret

Reputation: 382160

I suggest you change a little your schema to make this kind of queries trivial.

Suppose you add 5 columns to your cook table, to simply count the number of each ratings :

nb_ratings_1 nb_ratings_2 nb_ratings_3 nb_ratings_4 nb_ratings_5

Updating such a table when a new rating is entered in DB is trivial, just as would be recomputing those numbers if having redundancy makes you nervous. And it makes all filterings and sortings fast and easy.

Upvotes: 0

Related Questions