Gyanendra Kumar Gyanendra Kumar - 1 month ago 6
SQL Question

Return count of records in SQL

I have one table like


​SQL> select * from CRICKET_DETAILS;
TEAM1 TEAM2 WINNER
-------------------- -------------------- --------------------
INDIA PAKISTAN INDIA
INDIA SRILANKA INDIA
SRILANKA INDIA INDIA
PAKISTAN SRILANKA SRILANKA
PAKISTAN ENGLAND PAKISTAN
SRILANKA ENGLAND SRILANKA

6 rows selected.



I want output like this:

TEAM PLAYED WON LOST
ENGLAND 2 0 2
INDIA 3 3 0
PAKISTAN 3 1 2
​SRILANKA 4 2 2

Answer

I would go with group by and union all:

select team, count(*), sum(won), sum(lost)
from ((select team1 as team,
              (case when winner = team1 then 1 else 0 end) as won,
              (case when winner = team1 then 0 else 1 end) as lost
       from cricket_details cd
      ) union all
      (select team2,
              (case when winner = team2 then 1 else 0 end) as won,
              (case when winner = team2 then 0 else 1 end) as lost
       from cricket_details cd
      )
     ) tt
group by team;
Comments