TransWikia.com

mysql join 3 table but the "COUNT" be duplicate

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 ?

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP