Jump to content

Ratings in database ala Amazon. How many of each?


tunnelboy
 Share

Go to solution Solved by requinix,

Recommended Posts

I can't wrap my head around this one. If you look at the reviews of an item on Amazon, they show a little graph with something like:

5 stars - 107
4 stars - 23
3 stars -3
2 stars - 0
1 star - 3

I have a simple table along those lines.

key rating 
1 3
2 5
3 5
4 5
5 2
6 2
7 5

I want to count how many of each, so I do:

SELECT rating,sum(*) FROM reviews GROUP by rating

and I'll get something like this:

1 - 2
2 - 1
3 - 1
5 - 4

But I need to get that zero (0) from the 4 star rating (that nobody selected). I want:

1 - 2
2 - 1
3 - 1
4 - 0
5 - 4

Thanks

 

Link to comment
Share on other sites

Don't confuse sum and count.  It works here, but the two functions are very different.  Highly recommend you not get used to substituting sum for count as if they are interchangeable.  I also always alias computed columns so I can predict what the column name will be when fetched. 

SELECT rating, count(*) AS count_of FROM reviews GROUP BY rating

 

Now I heartily agree with @requinix  that a simple class or function is best here.  There is no way for the database to know the available universe of values with a scheme like this, which might facilitate an outer join solution to your problem. 

With that said, here is a SQL solution that makes a number of assumptions that might not be directly applicable to your database structure, but at least illustrates the idea.   I don't think this is a good solution for a number of reasons, including the fact, that you have to hardwire the ratings into your SQL statement AND you are doing a distinct query to figure out which values you need to exclude.  

There might be a better way to accomplish this  (maybe @Barand has something better?

This does work, and I provided everything you would need to verify in a test database.

create table reviews (id int unsigned PRIMARY KEY AUTO_INCREMENT, rating int);
insert into reviews(rating) values (5), (5), (2), (3), (1), (3), (2), (5), (1);

SELECT * FROM
(SELECT * FROM
(SELECT 1 as rating, 0 as count_of
union
SELECT 2 as rating, 0 as count_of
union
SELECT 3 as rating, 0 as count_of
union
SELECT 4 as rating, 0 as count_of
union
SELECT 5 as rating, 0 as count_of) as t1
WHERE t1.rating NOT IN (SELECT distinct(rating) as rating FROM reviews)
union
select rating, count(*) as count_of FROM reviews
GROUP BY rating
) as u1
ORDER BY rating ASC;

With this test data you get your desired result:

rating	count_of
1	2
2	2
3	2
4	0
5	3

 

Just to reiterate, a php function using range to pre-populate an array you then use to merge with your result, would be a great way to handle this in your PHP code, and that's what I would do here rather than utilize the convoluted solution I demonstrate here.

 

 

Link to comment
Share on other sites

Ok, I get it. How would the DB know there are 5 options? That makes total sense. To simplify things, I used Amazon's rating thing. In reality, this is a quiz composed of 15 questions each of varying lengths. Like the first question has 5 options, the 2nd has 4, etc. 

All I have to do is pass the number of expected results. Sometimes things are SO simple, they blind you!

Thanks very much both of you!

 

Link to comment
Share on other sites

12 hours ago, gizmola said:

maybe @Barand has something better?

Create a "valid_ratings" table in your DB containg a row for each valid rating (1-5). Useful for creating dropdowns or radio button lists for the user to choose from.

If you don't want to this, you can create it as a temporary table when you want thse totals

    #
    #  Additional valid ratings table
    #
    $pdo->exec("create temporary table valid_rating (rating int unsigned not null primary key)");
    $pdo->exec("insert into valid_rating values (1), (2), (3), (4), (5)");
    
    #
    #  get ratings totals
    #
    $res = $pdo->query("SELECT v.rating
                             , count(r.id) as total
                        FROM valid_rating v
                             LEFT JOIN
                             reviews r USING (rating)
                        GROUP BY rating;          
                       ");
                       
    printf("<pre>\n| Rating | Total |\n\n");
    foreach ($res as $r)  {
        printf("| %6d | %5d |\n", $r['rating'], $r['total']);
    }
    
| Rating | Total |

|      1 |     2 |
|      2 |     2 |
|      3 |     2 |
|      4 |     0 |
|      5 |     3 |

An alternative solution is the one @gizmola alluded to

    $results = array_fill_keys(range(1,5), 0);          // create array with 0 total for all possible ratings
    
    $res = $pdo->query("SELECT rating
                             , count(*) as total
                        FROM reviews
                        GROUP BY rating
                       ");
    foreach ($res as $r) {
        $results[$r['rating']] = $r['total'];           // put totals from query into the array
    }

    printf("<pre>\n| Rating | Total |\n\n");
    foreach ($results as $rating => $total) {
        printf("| %6d | %5d |\n", $rating, $total);
    }

 

  • Great Answer 1
Link to comment
Share on other sites

You can use a subquery to get the list of all possible ratings and then left join it with your reviews table to get the count of each rating. Here's an example query that should work:

SELECT ratings.rating, COALESCE(reviews.count, 0) as count FROM (
    SELECT DISTINCT rating FROM reviews
) as ratings
LEFT JOIN (
    SELECT rating, COUNT(*) as count FROM reviews GROUP BY rating
) as reviews
ON ratings.rating = reviews.rating

This query first gets a list of all distinct ratings from the reviews table, and then left joins it with a subquery that gets the count of each rating from the reviews table. The COALESCE function is used to replace any NULL values in the count column with 0, in case a rating doesn't have any reviews.

Link to comment
Share on other sites

To save time, let's just assume that sometimes you are.

Selecting "DISTINCT rating" from the input file created by gizmola will yield 1, 2, 3, 5 as there are no ratings of "4". This was the initial problem and your solution does nothing to solve it.

mysql> SELECT ratings.rating, COALESCE(reviews.count, 0) as count FROM (
    ->     SELECT DISTINCT rating FROM reviews
    -> ) as ratings
    -> LEFT JOIN (
    ->     SELECT rating, COUNT(*) as count FROM reviews GROUP BY rating
    -> ) as reviews
    -> ON ratings.rating = reviews.rating;
+--------+-------+
| rating | count |
+--------+-------+
|      5 |     3 |
|      2 |     2 |
|      3 |     2 |
|      1 |     2 |
+--------+-------+

 

Link to comment
Share on other sites

4 hours ago, Barand said:

To save time, let's just assume that sometimes you are.

Sometimes yes, but in this case (and my other posts last night) no since it wasn't my response. Allow me to explain...

I have been experimenting with the ChatGPT AI (Artificial Intelligence). "My" post was 100% generated by the ChatGPT AI based on the full text of the OP. Obviously AI still has a ways to go and can be absolutely wrong or even provide "dangerous" code solutions. Still, it is pretty cool what it can do at this stage. You can check it out here https://openai.com/blog/chatgpt/

Link to comment
Share on other sites

For starters, the OP basically lied. The OP has ABSOLUTELY NOTHING to do with "Ratings in database ala Amazon". The OP admits it later on..

"In reality, this is a quiz composed of 15 questions each of varying lengths."

And no, I didn't know it was incorrect for what the op was really trying to do. Had it really been a rating system, the query does indeed work to show ratings. Nevertheless, I won't be using AI content in future posts.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.