You can rank the results by assigning a row number and ordering by the calculated average.
Unfortunately MySQL doesn't have a built-in row number function (yet) but I found a neat trick by OMG Ponies on this
link[
^]
Demo:
I created a table thus:
create table myTable
(
id int not null auto_increment,
aValue char(30),
primary key (id)
);
INSERT INTO myTable (aValue) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
The data isn't that important but I took care that the values were not in alpha order.
This query
SELECT t.*,
@rownum := @rownum + 1 AS rank
FROM myTable t,
(SELECT @rownum := 0) r
order by aValue desc;generates these results
id aValue rank
5 whale 1
3 penguin 2
6 ostrich 3
4 lax 4
1 dog 5
2 cat 6
but if all I do is change the order
order by aValue asc;
I get
id aValue rank
2 cat 1
1 dog 2
4 lax 3
6 ostrich 4
3 penguin 5
5 whale 6
I.e. the "rank" is dynamic
[EDIT after OP comment]
I think your query needs to look like this:
SELECT IFNULL((SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation) + SUM(hygine) + SUM(treatment))/(count(hospitalID) * 6), 0) AverageRating,
COUNT(ID) RatingCount,
@rownum := @rownum + 1 AS rank
FROM ratings t,
(SELECT @rownum := 0) r
WHERE hospitalID = '1111'
order by 1 desc
Note - this is untested as I don't have any sample data to work from.
[EDIT 2]
Now that I've seen the sample data, this solution works.
Firstly I had to create a "temporary" table because you have stored numeric data as type varchar. You should never do this - always use the appropriate datatype for the data that will be in the column. Varchar/char should only be used for alphanumeric data (like names for example).
CREATE TABLE rateTemp
(
`hospitalID` varchar(100) NOT NULL,
`AverageRating` DECIMAL(15,2),
RatingCount INT
);
INSERT INTO rateTemp
SELECT hospitalID, CAST(IFNULL((SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation) + SUM(hygine) + SUM(treatment))/(count(hospitalID) * 6), 0) AS decimal(15,2)) AverageRating,
COUNT(ID) RatingCount
FROM ratings t
GROUP BY hospitalID
I can then use the technique I used above to generate the rank of each hospital and join that result back to the original table
SELECT DISTINCT A.hospitalID, B.AverageRating, B.RatingCount, rank
FROM
ratings A
INNER JOIN
(SELECT @rownum := @rownum + 1 AS rank, hospitalID, AverageRating, RatingCount
FROM rateTemp t, (SELECT @rownum := 0) r
ORDER BY AverageRating DESC
) B ON A.hospitalID=B.hospitalID
WHERE A.hospitalID = '446795'
For this example I got the result Rank = 4.
Note - if you don't fix your original table schema then you will have to recreate the rateTemp table each time. If you do fix your table schema then you can probably get rid of rateTemp altogether
Submit an article or tip