CODEWITHSUNDEEP
mysqlsql
Sunil Lohar
Sunil Lohar

Reputation: 2242

Calculate round(avg) from same table and join

I have two tables,

users

userid   fname    usertype
1        Steve    vendor
2        Peter    vendor
3        John     normaluser
4        Mark     normaluser
5        Kevin    vendor
6        Alan     vendor
7        Sean     vendor

vendor_rating

id   userid   rating
1    1        4
2    2        3
3    2        2
4    2        4
5    1        3
6    5        2
7    5        2

userid is foreign key.

i want to show all vendors (only usertype vendor) from user table by descending/ascending average rating even if Vendor's rating is not available on table it should show, its information should display at last in descending, at first in ascending.

I want to fetch all users info from first table so i m using left join :

  SELECT
  users.name,
  users.userid,
  users.usertype
FROM users
  LEFT JOIN (SELECT
      ROUND(AVG(rating)) AS rating_avg,
      userid
    FROM vendor_rating
    ORDER BY rating_avg DESC) ven
    ON users.usertype = 'vendor'
    AND users.userid = ven.userid
ORDER BY ven.rating_avg ASC;

Please help where am i going wrong.

EDIT:

I get this

userid  ROUND(AVG(vr.ratings))

28          5
27          4
16          3
26          2
25          0
NULL    NULL
NULL    NULL
NULL    NULL
NULL    NULL

if i use 

 SELECT vr.userid, ROUND(AVG(vr.ratings)) FROM vendor_rating vr
 RIGHT JOIN (SELECT users.fname, users.userid, users.usertype FROM users) u 
ON u.id = vr.vendor_id WHERE u.usertype = 'vendor' GROUP BY vr.userid,u.fname 
ORDER BY round(avg(vr.ratings)) ASC

i get NULL values from users table whose rating is not available in vendor_rating table those should display userids

Upvotes: 0

Views: 208

Answers (2)

Sunil Lohar
Sunil Lohar

Reputation: 2242

finally i got it

SELECT users.fname, users.userid,users.usertype
FROM users  
LEFT JOIN (select ROUND(AVG (ratings)) AS rating_avg,userid FROM 
vendor_rating group by userid order by rating_avg desc ) ven 
ON users.id=userid 
WHERE users.usertype='vendor'
order by rating_avg desc

Thank you all, for sharing views to get idea to solve my problem.

Upvotes: 0

Mgnfcnt
Mgnfcnt

Reputation: 129

Try to this

SELECT
  vr.userid,
  u.fname,
  ROUND(AVG(vr.rating))
FROM vendor_rating vr
  INNER JOIN users u
    ON u.userid = vr.userid
WHERE u.usertype = 'vendor'
GROUP BY vr.userid,
         u.fname
ORDER BY round(avg(vr.rating)) ASC

Upvotes: 1

Related Questions