Reputation: 45
I want to calculate average rating of every user.
I have three table.
table rating
id |order_id |rate
------------------
1 | 52 |8
2 | 51 |4
3 | 52 |2
4 | 51 |7
Table order
here did is driver table id
id |did
------------------
52 | 5
51 | 7
35 | 6
44 | 8
Table driver
id |name
------------------
5 | test1
7 | test2
8 | test3
6 | test4
my sql query is
select dr.name,AVG(drate.rate) from `rating` as drate,`order` as ord,`driver` as dr where ord.did=dr.id and drate.order_id=ord.id
my query always give result only one .
here i want to select average rate from rating table but relation is rating table order_id= order table id and order table did =driver table id
that means
order_id(rating table) |id(order table) |id(driver table) |rate
-------------------------------------------------------------------
52 | 52 |5 |avg(8,2)
51 | 51 |7 |avg(4,7)
Upvotes: 3
Views: 5612
Reputation: 8072
If order.did is user Id, then
select order.did, AVG(rate) from rating
join order on order.id = drate.id
group by order.did
Upvotes: 4