Help on this, Select values from Mysql table using Sum()

I have two tables, Competitors table and Competitors rating table

=>(Competitors and Ratings)

I want to get all rows from Competitors table where its id is equal to Ratings id table,
In order by Sum(ratings.total_ratings) DESC

I have this done but didn’t get it:

SELECT competitors* FROM competitors INNER JOIN ratings WHERE competitors.id AND ratings.id =’$id’ ORDER BY SUM(ratings.total_ratings) DESC

The code above didn’t work…please any help to a resolve will be appreciated! Thanks.

Hello,

You’re missing a GROUP BY: http://sqlfiddle.com/#!9/082af2/16/0

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?

Thanks again!

But using the last query I posted:

# 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…

Any help?

Thanks once again!