2

Let's say I have a sample data set, p1, where different users have rated 4 different types of food, like so:

Food_ID Rating
1       Good 
1       Good
2       Good
2       Bad
1       Bad
3       Bad
3       Good
4       Bad
1       Bad
4       Good
4       Good
4       Good
2       Bad
3       Bad

How can I write a code that tells me the acceptance rating of Food_ID based on the Good vs Bad ratings?

SELECT 
Food_ID, 
sum(Rating)/count(Rating) AS Avg_Rating

FROM p1
GROUP BY Food_ID

Obviously this code won't work because rating is a character column...is there a quick way to convert it to Boolean? Then perhaps it might? Or is there a simpler way altogether?

Thanks!

EDIT UPDATE:

Thanks to answers below, I've made progress. The following code:

sqldf("
      SELECT food_id, 
sum(CASE WHEN rating = 'Good' THEN 1 ELSE 0 END) AS good_reviews, 
count(*) AS all_reviews, 
sum(CASE WHEN rating = 'Good' THEN 1 ELSE 0 END) / count(*) AS acceptance

FROM p1

GROUP BY food_id

  ")

Which returns the following output:

  food_id good_reviews all_reviews acceptance
     1            2           4          0
     2            1           3          0
     3            0           3          0
     4            3           4          0
Tester_Y
  • 367
  • 4
  • 18

4 Answers4

2

Final correct answer -- the issue was stemming from two integers being divided and producing a non-integer result. Thanks @stickybit

Final working code:

sqldf("
  SELECT food_id, 
sum(CASE WHEN rating = 'Good' THEN 1 ELSE 0 END) AS good_reviews, 
count(*) AS all_reviews, 
CAST(sum(CASE WHEN rating = 'Good' THEN 1 ELSE 0 END) AS Float) / CAST(count(*) AS Float)*100 AS acceptance

FROM p1
GROUP BY food_id

  ")
Tester_Y
  • 367
  • 4
  • 18
  • One CAst to float is enough, but when you multiply before division you don't need to CAST at all, e.g.: `100 * sum(CASE WHEN rating = 'Good' THEN 1 ELSE 0 END) / count(*)` returns an Integer percentage, if you want fractional digits use `100.00` instead of `100` – dnoeth Jun 03 '18 at 09:03
0
SELECT 
  food_id, 
  SUM(CASE WHEN rating = 'Good' THEN 1 ELSE 0 END) AS good_reviews, 
  COUNT(*) AS all_reviews, 
  CAST(Sum(CASE WHEN rating = 'Good' THEN 1 ELSE 0 END) AS Float) 
    / CAST(COUNT(*) AS Float) * 100 AS acceptance
FROM p1
GROUP BY food_id
Z .
  • 12,657
  • 1
  • 31
  • 56
  • Thanks for your answer! I find that the first part works up until I divide it by count(). If I erase that part, I get the sum of all positive reviews only, which is great. When I do /count(), they all become 0 for some reason – Tester_Y Jun 03 '18 at 03:53
  • 1
    @Tester_Y: As it's a division of integers the DBMS might produce the result also as integer. Try to cast the division's operands to a more appropriate type. (Since you didn't tag your DBMS, I cannot give a more precise advise.) – sticky bit Jun 03 '18 at 04:05
  • I'm just using R, I created the database using excel and exported as CSV. Maybe the integer thing is the reason it's just returning 0 instead of a fraction.. – Tester_Y Jun 03 '18 at 04:08
  • @stickybit you're absolutely right. I changed all values of id=1 to Good, and it returns an acceptance of 1. How can I set it so the acceptance column will produce a fraction or percentage? – Tester_Y Jun 03 '18 at 04:12
0
SUM(CASE WHEN Rating='Good' THEN 1 ELSE 0 END AS BIT)/COUNT(*) AS numericalRating
Eric Nail
  • 160
  • 5
0

use IF

select 
   food_id, 
     sum(IF rating='good' then 1 else 0 end)/count(*) as avg_rating
 from p1
 group by food_id
Reza Mousazadeh
  • 148
  • 1
  • 10