CODEWITHSUNDEEP
mysql
Gobind Kumar Chauhan
Gobind Kumar Chauhan

Reputation: 35

Find how many times team has won and loss the matches?

Find how many times team has won and loss the matches ? 

 Find how many times team has won and loss the matches ?
My Table Structure

TeamA | TeamB | Won
A     | D     | D
B     | A     | A
A     | D     | A

Result should be like below

TeamName | Won | Lost
A        | 2   | 1
B        | 0   | 1
D        | 1   | 1

Upvotes: 2

Views: 7001

Answers (5)

xQbert
xQbert

Reputation: 35323

Conditional aggregation in combination with a union to normalize the data.

SELECT A.Team as TeamName, 
 SUM(CASE WHEN A.Team = A.Won then 1 else 0 end) as Won
 SUM(CASE WHEN A.Team <> A.Won then 1 else 0 end) as Lost
FROM (SELECT TeamA as Team, Won
      FROM TABLE
      UNION ALL
      SELECT TeamB as Team, Won 
      FROM Table
     ) A
GROUP BY A.Team

Upvotes: 3

Gurubaksh Singh
Gurubaksh Singh

Reputation: 49

You will get correct result by using this query:

SELECT team.t AS TeamName
     , (SELECT  COUNT(*) 
        FROM TeamTable WHERE won = team.t) AS won
     , (SELECT  WHERE won != team.t  AND (TeamA = team.t || TeamB = team.t)) AS Lost 
        FROM (SELECT = TeamA AS t FROM TeamTable  
              UNION 
              SELECT TeamB AS t FROM TeamTable) team

Upvotes: 1

Manoj Kumar Dhakad
Manoj Kumar Dhakad

Reputation: 1892

Input Table Name: matches

  1. First : count team wise total match played.

    select team,count(*) as total_matches from (select teamA as team from matches union All select teamB as team from matches) group by team

  2. Second : count team wise total match won

    select winner,count(*) as total_win from matches group by winner

  3. Third: Now you have total_matches, total_win, total_loss=total_matches- total_win using left outer join

    select t.team ,t.total_matches,coalesce(w.total_win,0) as total_win,(t.total_matches-coalesce(w.total_win,0)) as total_loss 
from (select team,count(*) as total_matches from (select teamA as team from matches union All select teamB as team from matches) group by team ) t 
left outer join (select winner,count(*) as total_win from matches group by winner) w 
on t.team=w.winner

Upvotes: 0

Nikhil
Nikhil

Reputation: 61

Extending your question for number of draws as well ( represented by '-' )

@xQbert answer was perfect.. just replace SUM with COUNT for better(handling nulls) and a bit faster results

SELECT A.Team as TeamName,
 COUNT(CASE WHEN A.Team=A.Won then 1 end) as Won,
 COUNT(CASE WHEN A.Team<>A.Won AND A.Won<>'-' THEN 1 END) as Lost,
 COUNT(CASE WHEN A.Won='-' then 1 END) as Draws
FROM (SELECT TeamA as Team, Won
    FROM TABLE
    UNION ALL
    SELECT TeamB as Team, Won 
    FROM Table
    ) A
GROUP BY A.Team

Upvotes: 1

Rams
Rams

Reputation: 2169

You need to first fetch all teams by using union of teamA and teamB.

Once that is done then you can have count of winning teams by doing group by operation on won team.

If team won is null it means it lost and not null means if has won and count of that is nothing but no.of times that won the game. Below is the query for the same

Select t.team, case when won is not null then won else 0 end as won,  case when won is null then 1 else 0 end as lost from (Select teamA aa team from table Union Select teamB from table) t left join (Select won, count(won)  won from table group by won) m on t.team=m.won

Upvotes: 0

Related Questions