How to query counting specific wins of team and find the winner of the series

Question

Table name: series_type

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)

Table name: matches

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

Output: desired
filter using league_id, start_time, and radiant_name and dire_name
ex.

Team "LV" total series wins 3.
Team "LGD" total series wins 2.
Series winner is LV.

Output: i have tried

Using Group by SERIES_ID and SUM but the results is different.

ex. query

SELECT SUM(IF(radiant_win = 1? 1, 0)) as LV, SUM(IF(radiant_win = 1? 0,1)) as LGD

ex. not desired results ~_~

Team "LV" wins 1.
Team "LGD" wins 4.

Update (Thanks to https://stackoverflow.com/users/3685967/bsting)
This query gives me correct results but theres 1 problem it gives 2 columns. i need it in 1 row
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 matches 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;

Query Results current query

winner| radiant_team_id| dire_team_id| series_id| series_type| count|
   LGD|         1848158|           15|      8313|           2|     2
    LV|         1848158|           15|      8313|           2|     3

Query Results desired query

winner|loser|  radiant_name|   dire_name|   series_id| series_type| radiant_count| dire_count|
    LV|  LGD|           LV |         LGD|        8313|           2|             3|          2|

To merge multiple rows of the result into a single row, see this [question on SO](http://stackoverflow.com/questions/1067428/combine-multiple-child-rows-into-one-row-mysql). It seems to be tricky to get this in MySQL. — René Hoffmann, Nov 21 '14 at 14:52

bsting · Accepted Answer · 2014-11-21T06:58:41.670

4

I will using case, count and group by

select winner, count(winner) from 
   (select case radiant_win 
             when 1 then radiant_name 
             else dire_name 
           end as winner
     FROM test.`match` ) as temp
group by winner;

test.'match' is a testing table i created in MySQL

Update

I can't find start_time, radiant_team_id and dire_team_id as what you posted in the comment after you updated your question.

Below answer might not be the one you want since I'm not so clear on your question after you updated it.

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 test.`matches` 
    where league_id = 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;

edited Nov 21 '14 at 06:58

answered Nov 21 '14 at 05:16

bsting

154
4

HI THANKS How to use that query when i have this. – ronscript Nov 21 '14 at 05:40
SELECT `radiant_team_id`,`dire_team_id`,`series_id`,`series_type` FROM matches WHERE `leagueid` = 2096 AND `start_time` > 1415938900 AND (`radiant_team_id`= 1848158 AND `dire_team_id`= 15) OR `leagueid` = 2096 AND (`radiant_team_id`= 15 AND `dire_team_id`= 1848158) AND `start_time` > 1415938900 – ronscript Nov 21 '14 at 05:41
@user2801777 the fields that you select out from above query are different from the one you post in your question. Do you mind to provide more info or open another thread and have it question out, thanks. – bsting Nov 21 '14 at 05:49
hey i have edited the tables can you review it again . thanks for the help – ronscript Nov 21 '14 at 06:16
HEY! SUPER THANKS THE RESULTS WAS CORRECT. But it gives 2 columns of result. i need to make it in 1 row only. Thanks for the help! :D. Please can you help me to make the results to give in 1 row only – ronscript Nov 21 '14 at 12:34
@ronscript you could order it by count in asc or desc and determine which row is winner/loser – bsting Nov 22 '14 at 09:03
still it gives 2 results. can you make query that returns the two teams in 1 result? thanks for the help – ronscript Nov 22 '14 at 09:05
please take a look. http://dba.stackexchange.com/questions/17921/combine-column-from-multiple-rows-into-single-row this is the same result i want.. – ronscript Nov 22 '14 at 09:08
@ronscript the example that you given is different from what you want, given example combine/concatenate records of records from multiple rows of same field and the one you want is to creating new fields (winer, loser, winner team id, loser team id, etc)... are you plan to do this in a user defined function/stored procedure or you want the result return from just a single statement? – bsting Nov 22 '14 at 10:03
what do you is the best way to do? create a new table and store it in there? or return from single statement? dont know what is the best – ronscript Nov 22 '14 at 10:25

How to query counting specific wins of team and find the winner of the series

1 Answers1

Linked