Stupid Romeo Stupid Romeo - 7 months ago 11
SQL Question

My SQL - Refer data from another table rows

I have no idea whether it is possible or not.
I have two tables:

Columns in hospitals Table


  • hospitalID

  • Name

  • AverageRating



Columns in ratings Table


  • ID

  • rating1

  • rating2

  • rating3

  • rating4

  • rating5

  • rating6

  • hospitalID



rating1
to
rating6
is types of ratings. Now By
calculation
I am able to get
average rating
of
particular hospital
in
ratings
table from following SELECT Query

SELECT IFNULL((SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation)
+ SUM(hygine) + SUM(treatment))/(count(hospitalID) * 6), 0
) AverageRating,COUNT(ID) RatingCount
FROM ratings
WHERE hospitalID = '111111'


Above query works for me perfectly but this
averageRating
I also want to calculate in my hospitals table because I want to ranks hospitals.

Is there any functions in mySQL through which I can calculate average rating to hospitals table by referring ratings table.

Rating Table
enter image description here

Query Output Table
enter image description here

A J A J
Answer

Based on the given table structure, you can try this.

SELECT IFNULL((SUM(r.charges) + SUM(r.behaviour) + SUM(r.admission) + SUM(properInformationr.) 
               + SUM(r.hygine) + SUM(r.treatment))/(COUNT(r.hospitalID) * 6), 0
              ) AverageRating
FROM ratings r INNER JOIN hospitals h ON r.hospitalID=h.hospitalID
WHERE h.hospitalID = '111111'

EDIT

SELECT avgratings.*, @curRow := @curRow + 1 AS hospitalRank
FROM (
    SELECT (SUM(r.`rating1`)+SUM(r.`rating2`)+SUM(r.`rating3`)+SUM(r.`rating4`)+SUM(r.`rating5`)+SUM(r.`rating6`))/(COUNT(r.`hospitalID`)*6) AS AverageRating, h.hospitalID
    FROM hospitals h INNER JOIN ratings r
    ON h.`hospitalID`=r.`hospitalID`
    WHERE 1 GROUP BY r.`hospitalID` 
) avgratings JOIN (SELECT @curRow := 0) rank
ORDER BY avgratings.AverageRating DESC

2nd Query to get rank of a particular hospital.

SELECT hospitalRank
FROM (
    SELECT avgratings.*, @curRow := @curRow + 1 AS hospitalRank
    FROM (
        SELECT (SUM(r.`rating1`)+SUM(r.`rating2`)+SUM(r.`rating3`)+SUM(r.`rating4`)+SUM(r.`rating5`)+SUM(r.`rating6`))/(COUNT(r.`hospitalID`)*6) AS AverageRating, h.hospitalID
        FROM hospitals h INNER JOIN ratings r
        ON h.`hospitalID`=r.`hospitalID`
        WHERE 1 GROUP BY r.`hospitalID` 
    ) avgratings JOIN (SELECT @curRow := 0) rank

) tablea
WHERE tablea.hospitalID=1 ORDER BY tablea.AverageRating DESC