CODEWITHSUNDEEP
mysqlgroup-bysum
akkonrad
akkonrad

Reputation: 1073

Mysql - group by result by SUM of 2 columns

I have 3 tables:

user(id, name, id_school)
school(id, name)
result(id_user, stage1, stage2)

Now I would like to get school ranking - is sum of 2 columns: stage1 and stage2 of all users.

Upvotes: 0

Views: 347

Answers (3)

Imre L
Imre L

Reputation: 6249

seems too simple:

select s.name as school, sum(stage1)+sum(stage2) as rank
  from result r 
  join user u on u.id=r.id_user 
  join school s on s.id=u.id_school
 group by s.id

and i really hope you have indexes.

Upvotes: 2

Wessel
Wessel

Reputation: 81

I would do something like this:

SELECT school.name, SUM(result.result1 + result.result2) 
FROM school LEFT JOIN user ON (user.id_school = school.id) LEFT JOIN result ON (result.id_user = user.id) GROUP BY school.id

Hope it helps, good luck :)

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try this query. It is only assumption.please provide some data for testing

SELECT 
    s.id,
    s.name AS SchoolName
    (r.S1 + r.S2) AS Rank
FROM school as s
LEFT JOIN user as u ON u.id_school = s.id
LEFT JOIN (SELECT id_user , SUM(stage1) as S1 , SUM(stage2) FROM result GROUP BY id_user) as r ON r.id_user = u.id
GROUP BY s.id
ORDER BY Rank DESC

Upvotes: 0

Related Questions