Stupid Romeo - 9 months ago 18

SQL Question

I have no idea whether it is possible or not.

I have two tables:

Columns in **hospitals** Table

- hospitalID
- Name
- AverageRating

Columns in

- ID
- rating1
- rating2
- rating3
- rating4
- rating5
- rating6
- hospitalID

`rating1`

`rating6`

`calculation`

`average rating`

`particular hospital`

`ratings`

`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`

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

Rating Table

Query Output Table

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
```