CODEWITHSUNDEEP
phpmysqlsqldatabasepdo
Abdurrehman
Abdurrehman

Reputation: 79

Get Sum of score of a team which is present in multiple rows and two columns MySql PHP

I want to get the total score of a team which can be in multiple rows and 2 columns (1: FirstTeamName, 2: SecondTeamName) at max for any given EventName. It will be a PHP function given only eventName as parameter

Please see the attached image of table

E.g: In this case total score of Team 2 is = 1+4+3 = 8

Thank you

Upvotes: 1

Views: 110

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You can unpivot and aggregate:

select eventname, team, sum(score)
from ((select eventname, firstteamname as team, firstteamscore as score
       from t
      ) union all
      (select eventname, secondteamname as team, secondteamscore as score
       from t
      ) 
     ) t
group by eventname, team;

If you want to filter on a particular event, I would recommend doing that in the two subqueries.

Upvotes: 1

Related Questions