create table competitors (
id int(11) primary key auto_increment,
name varchar(80) not null
)engine=innodb;
create table ratings (
id int(11) primary key auto_increment,
total_ratings int(11) not null default 0,
competitor_id int(11) not null,
foreign key (competitor_id) references competitors(id)
)engine=innodb;
insert into competitors (name) values ('skaparate'), ('marcus');
set @cid = (select id from competitors where name = 'skaparate');
insert into ratings (total_ratings, competitor_id) values
(2, @cid),
(2, @cid),
(3, @cid),
(4, @cid),
(5, @cid),
(3, @cid),
(3, @cid),
(3, @cid);
set @cid = (select id from competitors where name = 'marcus');
insert into ratings (total_ratings, competitor_id) values
(2, @cid),
(2, @cid),
(3, @cid),
(4, @cid),
(3, @cid),
(3, @cid),
(2, @cid),
(2, @cid);
# Query:
select
competitors.name
from competitors
inner join ratings on ratings.competitor_id = competitors.id
group by competitors.name
order by sum(ratings.total_ratings) desc
# It can be written like this too:
select
competitors.name,
sum(ratings.total_ratings) as total
from competitors
inner join ratings on ratings.competitor_id = competitors.id
group by competitors.name
order by total desc
Thanks! Yes indeed group is what i need and it showed the required rows, but i want the list to be grouped by their ratings, the row with the highest rate stays up… I tried grouping it by rate it didn’t work… Any help?
# The only difference is that I added the total column to the result
select
competitors.name,
sum(ratings.total_ratings) as total
from competitors
inner join ratings on ratings.competitor_id = competitors.id
group by competitors.name
order by total desc
Results in:
name | total
skaparate | 25
marcus | 21
Isn’t that what you need? I mean, they’re grouped by rating (the total rating)
Yea, i did the same but i coudn’t get it as i wanted, here is my real syntax;
$query= “select * , sum(sp_ratings.rating) as total from competitors inner join sp_ratings where competitors.comp_id = sp_ratings.comp_id and competitors.comp_id = ‘$compID’ group by competitors.comp_title order by total desc”
It only fetches the required values but not in order by total …thats it…