How to get count of occurrence of specific value for each row SQL

Question

I have a table named ReviewFact which has the following attributes:

- CategoryID (Primary Key)
- Star_Rating (Primary Key)
- No_of_Reviews

CategoryID refers to specific product, Star_Rating refers to a star rating given by users, No_of_Reviews refers to how many users gave this rating.

I have to write an SQL query which shows the number of 5 star reviews for each category.

I have come up with the following:

SELECT r.CategoryID, r.NUMBER_OF_REVIEWS
FROM REVIEWFACT r
WHERE r.Stars = 5
ORDER BY r.NUMBER_OF_REVIEWS desc;

Using this I get the number of reviews for rows which have 5 star reviews, but not for ones which don't. The ones which don't should have the count as 0. How do I go about solving this?

If there are the following rows in the table:

CategoryID        Star_Rating        No_of_Reviews
1                 5                  10
2                 5                  4
3                 2                  9

The query should return

CategoryID        No_of_Reviews
1                 10
2                 4
3                 0

Gordon Linoff · Accepted Answer · 2017-09-11 11:46:45Z

1

I think you just want a case:

SELECT r.CategoryID,
       (CASE WHEN r.starts = 5 THEN r.NUMBER_OF_REVIEWS ELSE 0 END)
FROM REVIEWFACT r
ORDER BY r.NUMBER_OF_REVIEWS desc;

answered Sep 11, 2017 at 11:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AndyrK Over a year ago

This works but it returns multiple records containing same CategoryID. If CategoryID 6 has two star rating reviews, 2 and 5, it returns a row for each containing count of 5 stars, but I just want it to contain count for five star (if it exists) or otherwise return 0 for each category ID.

Gordon Linoff Over a year ago

@AndyrK . . . That is how your sample data is constructed. If you have another question, then ask it as a question. If you change this question, you will invalidate the answers of people who answered the original one.

AndyrK · Accepted Answer · 2017-09-11 14:13:19Z

0

Group by category and sum conditionally:

select
  categoryid,
  sum(case when stars = 5 then no_of_reviews else 0 end) as no_of_reviews
from reviewfact 
group by categoryid
order by no_of_reviews;

edited Sep 11, 2017 at 14:13

AndyrK

71 silver badge7 bronze badges

answered Sep 11, 2017 at 12:17

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Comments

icoder · Accepted Answer · 2017-09-11 12:05:11Z

0

Same, with IF statement:

SELECT r.CategoryID, IF(r.Stars = 5, r.NUMBER_OF_REVIEWS, 0) as bestReviews
FROM REVIEWFACT r
ORDER BY r.NUMBER_OF_REVIEWS desc;

answered Sep 11, 2017 at 12:05

icoder

1652 silver badges5 bronze badges

Collectives™ on Stack Overflow

How to get count of occurrence of specific value for each row SQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related