title: How to query counting specific wins of team and find the winner of the series
body: <p><strong>Table name:</strong> <em>series_type</em></p>
  
  <pre><code>id| type| description 
  1 |    0| No series (Any team win 1 will be the winner)
  2 |    1| Best of 3 (Any team wins 2 will be the winner else draw)
  3 |    2| Best of 5 (Any team wins 3 will be the winner else draw)
  </code></pre>
  
  <p><strong>Table name:</strong> <em>matches</em></p>
  
  <pre><code>ID| series_id | series_type | league_id | start_time |radiant_name | dire_name | radiant_win
  1 |      8313 |           2 |      2096 |   xxxxxxx1 |          LV |       LGD | true 
  2 |      8313 |           2 |      2096 |   xxxxxxx2 |         LGD |        LV | false
  3 |      8313 |           2 |      2096 |   xxxxxxx3 |          LV |       LGD | false
  4 |      8313 |           2 |      2096 |   xxxxxxx4 |          LV |       LGD | false
  5 |      8313 |           2 |      2096 |   xxxxxxx5 |         LGD |        LV | false
  </code></pre>
  
  <p><strong>Output:</strong> <em>desired</em>
  <br>
  filter using league_id, start_time, and radiant_name and dire_name 
  <br>
  <strong>ex.</strong> <br></p>
  
  <pre><code>Team "LV" total series wins 3.
  Team "LGD" total series wins 2.
  Series winner is LV.
  </code></pre>
  
  <p><strong>Output:</strong> <em>i have tried</em></p>
  
  <p>Using Group by SERIES_ID and SUM but the results is different.</p>
  
  <p><strong>ex.</strong> <em>query</em>
  <br> </p>
  
  <pre><code>SELECT SUM(IF(radiant_win = 1? 1, 0)) as LV, SUM(IF(radiant_win = 1? 0,1)) as LGD
  </code></pre>
  
  <p><br>
  <strong>ex.</strong> <em>not desired results ~_~</em> <br></p>
  
  <pre><code>Team "LV" wins 1.
  Team "LGD" wins 4.
  </code></pre>
  
  <p><strong>Update (Thanks to <a href="https://stackoverflow.com/users/3685967/bsting">https://stackoverflow.com/users/3685967/bsting</a>)</strong><br>
  <em>This query gives me correct results but theres 1 problem it gives 2 columns. i need it in 1 row</em>
  <br>
  <code>
  select *, count(winner) as count 
  from (select case radiant_win 
              when 1 then radiant_name 
              else dire_name 
          end as winner, 
          radiant_team_id, 
          dire_team_id,
          series_id,
          series_type
      from <code>matches</code> 
      where leagueid = 2096 and 
            start_time >= 1415938900 and 
           ((radiant_team_id= 1848158 and dire_team_id= 15) 
             or (radiant_team_id= 15 and dire_team_id= 1848158)) 
      ) as temp
  group by winner;
  </code></p>
  
  <p><strong>Query Results</strong> <em>current query</em></p>
  
  <pre><code>winner| radiant_team_id| dire_team_id| series_id| series_type| count|
     LGD|         1848158|           15|      8313|           2|     2
      LV|         1848158|           15|      8313|           2|     3
  </code></pre>
  
  <p><strong>Query Results</strong> <em>desired query</em></p>
  
  <pre><code>winner|loser|  radiant_name|   dire_name|   series_id| series_type| radiant_count| dire_count|
      LV|  LGD|           LV |         LGD|        8313|           2|             3|          2|
  </code></pre>