CODEWITHSUNDEEP
sql
itailitai
itailitai

Reputation: 459

SELECT AVG subquery with condition

I have the follwing tables:

teachers , teacher_rating and cities.

teachers has the following columns:

teacherID,location,name,othercities,status

teacher_rating has the following columns:

ratingId, teacherId,content,ratingNumber,created,status,userName

cities has the following columns:

id,name

I'm trying to sort all of the teachers (rows from teachers where status=0) by their average rating, this is my php variable with the SQL:

$q="SELECT
    *,
    AVG(pr.ratingNumber) AS rating_average
FROM teachers as p
LEFT JOIN teacher_rating pr
ON pr.teacherId = p.teacherID
WHERE p.location=(SELECT `name` FROM `cities` WHERE `id`=:location) AND p.status=0 OR p.othercities REGEXP CONCAT('[[:<:]](', :location,')( |)[[:>:]]') AND p.status=0
GROUP BY p.teacherID
ORDER BY rating_average DESC
";

It works fine, the only problem is that the rating average includes ratings from the teacher_rating table where status=1, I want it to calculate the average rating only using the values from teacher_rating where status=0.

I'm not sure how to approach this problem, thanks for the help!

Upvotes: 0

Views: 62

Answers (2)

Rajat
Rajat

Reputation: 5803

How about adding pr.status=0 in the join?

ON pr.teacherId = p.teacherID AND pr.status=0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You are mixing AND and OR, so you should use parentheses. I think you intend:

WHERE p.status = 0 AND
      pr.status = 0 AND
      (p.location = (SELECT `name` FROM `cities` WHERE `id`=:location) OR 
       p.othercities REGEXP CONCAT('[[:<:]](', :location,')( |)[[:>:]]')
      )

Upvotes: 0

Related Questions