CODEWITHSUNDEEP
phpmysql
user3533255
user3533255

Reputation: 45

average rating calculation mysql php

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

Answers (1)

Alex
Alex

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

Related Questions