Ratings in database ala Amazon. How many of each?

[tunnelboy]

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

 

[requinix]

Does it have to be in your results? It would be really easy to just write a tiny bit of code that uses 0 if there aren't any ratings of a particular star count.

[gizmola]

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.

 

 

[tunnelboy]

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!

 

[Barand]

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);
    }

 

[benanamen]

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.

[Barand]

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 |
+--------+-------+

 

[benanamen]

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/

[Barand]

1 hour ago, benanamen said:

no since it wasn't my response

Of course it was your response. You posted it. It has your name on it.

So given how you obtained the "solution", and knowing is was incorrect, do think that posting an ai-produced, misleading solution was the responsible thing to do?

[benanamen]

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.

[Barand]

1 minute ago, benanamen said:

And no, I didn't know it was incorrect

That blows your signature's assumption out of the water.

The OP didn't claim to processing Amazon ratings but wanted to show ratings a la Amazon

[benanamen]

1 hour ago, Barand said:

That blows your signature's assumption out of the water.

It's just a signature Barand. We are just arguing at this point. Grab a pint and enjoy your day. See you on another thread. 🙂