# Ratings in database ala Amazon. How many of each?

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

##### Share on other sites

• Solution

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.

##### 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.

##### 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!

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

```    #
#
\$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);
}```

##### 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.

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

##### 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/

##### Share on other sites

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?

##### 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.

##### Share on other sites

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

##### Share on other sites

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. 🙂

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

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

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.