CODEWITHSUNDEEP
phpmysql
Muhammad Umar
Muhammad Umar

Reputation: 11782

Get Average value of from one table for a specific row in another table php

I am trying to do following.

Select a Hospital with id = hid from table named as hospital. Add a value "overall_rating" to it and get all the ratings and make avg of it from another table named as hrating

here is my query

 $statement = $conn->prepare('SELECT hospital.*,(SELECT AVG(hrating.rating_h) FROM hrating WHERE hid = hospital.hid) as overall_rating WHERE hid=:hid LIMIT 1');

Getting this error

{"error":"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE hid='44' LIMIT 1' at line 1"}

Where am i being wrong.

Upvotes: 0

Views: 70

Answers (2)

shola
shola

Reputation: 704

Try this:

SELECT hospital.*, temp.overall_rating FROM hospital LEFT JOIN (SELECT hid AVG(rating_h) as overall_rating FROM hrating group by hid ) temp
on hid = hospital.hid WHERE hospital.hid=:hid LIMIT 1

Upvotes: 0

jwwishart
jwwishart

Reputation: 2895

It appears you don't have a " FROM hospital " bit in your query?

SELECT hospital.*,(SELECT AVG(hrating.rating_h) 
                   FROM hrating 
                   WHERE hid = hospital.hid) as overall_rating 
FROM hospital -- this line seems to be missing ??
WHERE hid=:hid LIMIT 1

Upvotes: 2

Related Questions