CODEWITHSUNDEEP
mysqlsqldatabase
ronscript
ronscript

Reputation: 407

How to make mysql query from subqueries combine the 2 results in to 1 result

Questions:

How to make this query results combine into 1 result? 

    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;

Current Results

Below is the current query results

Results from the query

Desired Results

Below is my desired results

Desired results

Upvotes: 0

Views: 48

Answers (1)

Amir Rahimi Farahani
Amir Rahimi Farahani

Reputation: 1590

It seems you want to summarize all the matches between two teams and count how many times each of them was the winner, if so:

To simplify things, create a view:

create view v_matches as
select radiant_team_id as team1_id, radiant_win as team1_win, dire_team_id as team2_id, 1 - radiant_win as team2_win, leagueid, start_time, series_id, series_type
from matches
union 
select dire_team_id as team1_id, 1 - radiant_win as team1_win, radiant_team_id as team2_id, radiant_win as team2_win, leagueid, start_time, series_id, series_type
from matches

And then:

select team1_id, sum(team1_win) as team1_wins, team2_id, sum(team2_win) as team2_wins, series_id, series_type 
from v_matches
where leagueid = 2096 and start_time >= 1415938900 
  and team1_id = 1848158 and team2_id = 15

You can add team names to the view or join the second query with your teams table.

Upvotes: 1

Related Questions