Stack Overflow Asked by Faisal Budiman on January 24, 2021
i have 3 tables like this
orders table:
| id | product_id | status | created_at |
|----| ---------- | ------ | ---------- |
| 1 | 1 | done | 1607431726 |
| 2 | 7 | done | 1607431726 |
| 3 | 8 | done | 1607431726 |
products table:
| id | user_id | title | description | created_at |
|----| ------- | ----------- | ------------- | ---------- |
| 1 | 1 | product 1 | description 1 | 1607431726 |
| 7 | 3 | product 2 | description 1 | 1607431726 |
| 8 | 3 | product 3 | description 1 | 1607431726 |
ratings table:
| id | client_id | content_type | content_id | rating | created_at |
|----| --------- | ------------ | ---------- | ------ | ---------- |
| 1 | 5 | user | 1 | 5 | 1607431726 |
| 2 | 4 | user | 3 | 5 | 1607431726 |
| 3 | 5 | user | 3 | 4 | 1607431726 |
from 3 tables above, i want to get 1 result in which there is a field average_rating/user, total order/user, and i want to sort by average_rating & total_rating DESC. Which is roughly like this result :
| user_id | average_rating | total_order |
| ------- | -------------- | ----------- |
| 1 | 5.0 | 1 |
| 3 | 4.5 | 2 |
this is my query:
SELECT b.user_id, round(avg(c.rating), 1) as total_rating, COUNT(a.id) as total_order
FROM orders a
LEFT JOIN products b ON a.product_id=b.id
LEFT JOIN ratings c ON c.content_id=b.user_id
WHERE a.status = 'done'
AND c.content_type = 'user'
GROUP BY b.user_id, c.content_id;
but with my Query, the total order return 1 for user_id 1 and 4 for user_id 3, the result is :
| user_id | average_rating | total_order |
| ------- | -------------- | ----------- |
| 1 | 5.0 | 1 |
| 3 | 4.5 | 4 |
i have been try with INNER JOIN
, LEFT OUTER
, RIGHT OUTER
, RIGHT JOIN
, but the result is the same.
Can anyone help me please ?
There are multiple ratings per product. One option uses distinct
:
SELECT p.user_id,
round(avg(r.rating), 1) as total_rating,
COUNT(distinct o.id) as total_order
FROM orders o
INNER JOIN products p ON o.product_id=p.id
INNER JOIN ratings r ON r.content_id=p.user_id
WHERE o.status = 'done' AND r.content_type = 'user'
GROUP BY p.user_id, r.content_id;
Correct answer by GMB on January 24, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP