title: Print the points table of a cricket match using Mysql
body: <p>I am trying to solve this question using the join and nested queries. However I am facing issue while printing the final result as there are two rows which are not correctly printed. I am posting below question and the relevant details.</p>
  <p>Sql query to fetch the Team name, total number of matches Played by  each team, no of match won by the team and no of match lost by the team .</p>
  <p>Below is Schema and sample data:</p>
  <pre class="lang-sql prettyprint-override"><code>create table cricket(
      Match_no int,
      Team_a  varchar(20),
      Team_b  varchar(20),
      Winner  varchar(20)
  );
  
  Insert into cricket (Match_no,Team_a,Team_b,Winner) values 
  (1,'westindies','srilanka'  ,'westindies'), 
  (2  ,'india ','srilanka'    ,'india'),
  (3  ,'australia'    ,'srilanka' ,'australia'),
  (4  ,'westindies'   ,'srilanka',    'srilanka'),
  (5  ,'australia'    ,'india'    ,'australia'),
  (6, 'westindies'    ,'srilanka' ,'westindies'),
  (7  ,'india'    ,'westindies'   ,'westindies'),
  (8  ,'westindies'   ,'australia',   'australia'),
  (9  ,'westindies',  'india','india'),
  (10,'australia',    'westindies',   'westindies'),
  (11 ,'westindies'   ,'srilanka' ,'westindies'),
  (12 ,'india'    ,'australia',   'india'),
  (13,    'srilanka'  ,'newzealand',  'srilanka'),
  (14 ,'newzealand'   ,'india',   'india') 
  </code></pre>
  <p>I am using below query, but the matches won and lost is  not correct for SriLanka and Newzeland in my output.</p>
  <pre class="lang-sql prettyprint-override"><code>    select team.t as Teamname,count(1)as  Played ,
      SUM(CASE WHEN team.t = c.Winner THEN 1 ELSE 0 end ) as WON,
      (SUM(CASE WHEN team.t &lt;&gt; c.Winner THEN 1 ELSE 0 end ) ) as Lost
      from 
      (select  distinct Team_a as t from cricket where Team_a in (
      select Team_b as t from cricket)) team
      join cricket c on team.t in (c.Team_a,c.Team_b)
      group by team.t order by 2 desc;
  </code></pre>
  <p>My output:</p>
  <pre class="lang-none prettyprint-override"><code>Teamname    Played  WON Lost
  westindies    8      5   3
  srilanka      7      2   5
  india         6      4   2
  australia     5      3   2
  newzealand  2        0   2
  </code></pre>
  <p>Expected outcome:</p>
  <pre class="lang-none prettyprint-override"><code>Team_name   No_of_matches_played      Matches_won   Matches_lost
  westindies           8                   5          3
  india                6                   4          2
  australia            5                   3          2
  srilanka             7                   1          6
  newzealand           2                   1          1
  </code></pre>
  <p>Any help will be highly appreciated.</p>