mysql select multiple avg columns using where in condition and join query

Question

how to use where in condition with this query? like, WHERE institutions.instituteId IN ("1","2","4","15").

SELECT (SELECT avg(rating_score.score) FROM reviews INNER JOIN rating_score ON  reviews.reviewsId=rating_score.reviewsId WHERE reviews.instituteId='13' AND ratingItemId='1') as 'Acadamics', 
(SELECT avg(rating_score.score) FROM reviews INNER JOIN rating_score ON reviews.reviewsId=rating_score.reviewsId WHERE reviews.instituteId='13' AND ratingItemId='2') as 'Area_of_study',
(SELECT avg(rating_score.score) FROM reviews INNER JOIN rating_score ON reviews.reviewsId=rating_score.reviewsId WHERE reviews.instituteId='13' AND ratingItemId='3') as 'Campus_Facilities', 
(SELECT avg(rating_score.score) FROM reviews INNER JOIN rating_score ON reviews.reviewsId=rating_score.reviewsId WHERE reviews.instituteId='13' AND ratingItemId='4') as 'Acommadatoin',
(SELECT avg(rating_score.score) FROM reviews INNER JOIN rating_score ON reviews.reviewsId=rating_score.reviewsId WHERE reviews.instituteId='13' AND ratingItemId='5') as 'Sports_Facilities',
institutions.instituteId, institutions.instituteName, institutions.location, countries.countryName, institutions.siteAddress, institutions.overallRatings FROM institutions INNER JOIN countries ON institutions.countryId=countries.countryId 
WHERE institutions.instituteId='13'

If you change `SUM()` to `AVG()` it will return the average instead of sum. What's the problem you're having with the where condition? — Barmar, Apr 21 '18 at 06:32
i want average score of institutions but here only one instituteid so it returns one result. — kumar, Apr 21 '18 at 06:39
Change to `WHERE institutions.instituteId IN ("1","2","4","15")` and use the method in the linked question and it will work. — Barmar, Apr 21 '18 at 06:40
Also use `GROUP BY instituteId` to get a separate result for each institute. — Barmar, Apr 21 '18 at 06:41

score 1 · Accepted Answer · answered Apr 21 '18 at 06:51

This is similar to the solutions in need to return two sets of data with two different where clauses but uses AVG instead of SUM. The main difference is that you don't use ELSE 0 in the CASE, because you don't want to average 0 for all the rows with a different raingItemId. AVG() will just ignore those null rows.

SELECT 
    AVG(CASE WHEN ratingItemId='1' THEN rs.score END) AS Academics,
    AVG(CASE WHEN ratingItemId='2' THEN rs.score END) AS Area_of_study,
    AVG(CASE WHEN ratingItemId='3' THEN rs.score END) AS Campus_Facilities,
    AVG(CASE WHEN ratingItemId='4' THEN rs.score END) AS Acommadatoin,
    AVG(CASE WHEN ratingItemId='5' THEN rs.score END) AS Sports_Facilities,
    i.instituteName, i.location, c.countryName, i.siteAddress, i.overallRatings
FROM institutions AS i
INNER JOIN countries AS c ON i.countryId=c.countryId
LEFT JOIN reviews AS r ON r.instituteId = i.instituteId
LEFT JOIN rating_score AS rs ON rs.reviewsId = r.reviewsId
WHERE i.instituteId IN (1, 2, 4, 15)
GROUP BY i.instituteId

mysql select multiple avg columns using where in condition and join query

1 Answers1