My tables:
TblFruits
| id | name |
|---|---|
| 1 | apple |
TblReview
| reviewID | verdict | fruitID | userID | datePosted |
|---|---|---|---|---|
| 2 | Delicious | 1 | 1 | 2024-12-09 00:00:00 |
| 3 | Awful | 1 | 1 | 2024-12-07 00:00:00 |
| 5 | Delicious | 1 | 2 | 2024-12-09 00:00:00 |
| 6 | Delicious | 1 | 3 | 2024-12-09 00:00:00 |
| 7 | Awful | 1 | 4 | 2024-12-09 00:00:00 |
My final query needs to look like this:
| fruit name | Verdict | % |
|---|---|---|
| apple | Delicious | 75 |
I need to return a single row containing the majority verdict of the reviews and the percent of reviews that lead to that verdict.
Catch
Users can post multiple reviews of the same fruit, in which case only the latest review will be counted
Unfortunately I only got as far as joining the tables
<code>select name as 'fruit name', tbReview.verdict, tbReview.userID as 'reviewed by', tbReview.datePosted
from tbFruits
left join tbReview on tbFruits.fruitId = tbReview.fruitID
group by tbReview.userID, tbReview.datePosted, tbFruits.name, tbReview.verdict
</code>
<code>select name as 'fruit name', tbReview.verdict, tbReview.userID as 'reviewed by', tbReview.datePosted
from tbFruits
left join tbReview on tbFruits.fruitId = tbReview.fruitID
group by tbReview.userID, tbReview.datePosted, tbFruits.name, tbReview.verdict
</code>
select name as 'fruit name', tbReview.verdict, tbReview.userID as 'reviewed by', tbReview.datePosted
from tbFruits
left join tbReview on tbFruits.fruitId = tbReview.fruitID
group by tbReview.userID, tbReview.datePosted, tbFruits.name, tbReview.verdict
New contributor
Wohn Jick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.