How to make sql query returns only in 1 row? or How to merge or combine the 2 results into 1

Question

Question:

• How to make merge or combine the 2 team results into 1? Or
• How to make it returns only in 1 row?

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: teams

       ID| name 
  1848158| LV
       15| LGD

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

Query:

SELECT series_id, team, SUM(Win) As Won, SUM(Loss) as Lost
FROM
( SELECT *,radiant_name as team, 
     CASE WHEN radiant_win = 1 THEN 1 ELSE 0 END as Win, 
     CASE WHEN radiant_win = 1 THEN 0 ELSE 1 END as Loss
  FROM matches
  UNION ALL
  SELECT *,dire_name as team, 
     CASE WHEN radiant_win = 0 THEN 1 ELSE 0 END as Win, 
     CASE WHEN radiant_win = 0 THEN 0 ELSE 1 END as Loss
  FROM matches
) as temp
WHERE series_id = 8313
GROUP By team
ORDER By Won, Lost DESC

Results: when group by series_id

series_id| team| Won| Lost|
     8313|   LV|   5|    5|

Results: when group by team

series_id| team| Won| Lost|
     8313|  LGD|   2|    3|
     8313|   LV|   3|    2|

Expected Results: i need it group by series_id

series_id| teamA| teamB| teamAWon| teamBWon| teamALost| teamBLost|
     8313|    LV|   LGD|        3|        2|         2|         3|

Answer 1

The main complicating factor is that each team name can appear in radiant_name or dire_name 0 or more times. So it's a little complicated to pivot the teams into one column per team name. It would be easier if matches had a constant teamA and teamB column, and then a column to indicate which team was radiant and another column to indicate which team won.

This is almost certainly not optimal, but it appears to work:

-- create a two-column temp table with series ID and team name, insert rows from matches
-- we'll wind up with one row per distinct radiant_name and distinct dire_name,
-- so up to 4 rows for a series
CREATE TEMPORARY TABLE IF NOT EXISTS tempteama AS
select distinct series_id, radiant_name AS team
from matches
union
select distinct series_id, dire_name AS team
from matches;

-- do this again in a second temp table, so we can join the two later
-- (MySQL doesn't like to join temp tables against themselves)
CREATE TEMPORARY TABLE IF NOT EXISTS tempteamb AS
select distinct series_id, radiant_name AS team
from matches
union
select distinct series_id, dire_name AS team
from matches;

-- join matches against the temp table to get final results
select matches.series_id, 
teamA, 
teamB, 
sum(case when (radiant_name = teamA AND radiant_win=1) OR (dire_name=teamA AND radiant_win=0) then 1 else 0 end) AS teamA_win,
sum(case when (radiant_name = teamB AND radiant_win=1) OR (dire_name=teamB AND radiant_win=0) then 1 else 0 end) AS teamB_win
from matches
join (
-- select just one row from the temp table. That gives us the combination of teams for the series.
select tempteama.series_id, tempteama.team AS teamA, tempteamb.team AS teamB
from tempteama
join tempteamb on tempteama.series_id = tempteamb.series_id and tempteama.team != tempteamb.team
limit 1
) AS teamnames on teamnames.series_id = matches.series_id
group by matches.series_id, teamA, teamB;

You could probably do the temp table queries as sub-queries, at the expense of some readability.

Answer 2

I have two results for this question one is for any sql and other for oracle sql(as i used oracle function in it)

One can run this solution on any sql:-

SELECT AB.SERIES_ID,TEAMA,TEAMB ,TEAMAWON,COUNT(STC.SERIES_ID)-TEAMAWON TEAMBWON,
COUNT(STC.SERIES_ID)-TEAMAWON TEAMALOST,
COUNT(STC.SERIES_ID)-(COUNT(STC.SERIES_ID)-TEAMAWON) TEAMBLOST FROM  
(
  SELECT MA.SERIES_ID,MA.RADIANT_NAME TEAMA,MA.DIRE_NAME TEAMB ,
  COUNT(RADIANT_WIN) TEAMAWON FROM MATCHES MA,SERIES_TYPE ST
  WHERE  MA.SERIES_TYPE=ST.TYPE   
  GROUP BY MA.SERIES_ID,MA.RADIANT_NAME,DIRE_NAME
) AB, MATCHES STC 
WHERE (AB.SERIES_ID,AB.TEAMAWON) IN
  (
    SELECT SERIES_ID,MAX(RADIANT_WIN) RAD
    FROM
    (
        SELECT MA.SERIES_ID,MA.RADIANT_NAME ,COUNT(RADIANT_WIN) RADIANT_WIN 
        FROM  MATCHES MA,SERIES_TYPE ST
        WHERE  MA.SERIES_TYPE=ST.TYPE   
        GROUP BY MA.SERIES_ID,MA.RADIANT_NAME,DIRE_NAME
    )
     GROUP BY SERIES_ID
 ) AND STC.SERIES_ID=AB.SERIES_ID AND  TEAMA IN (RADIANT_NAME,DIRE_NAME)
 GROUP BY AB.SERIES_ID,TEAMA,TEAMB ,TEAMAWON;

Now i m writing the Solution for the oracle people who are seeking for the same result using oracle :-

SELECT SERIES_ID,GREATEST(RADIANT_NAME,DIRE_NAME) TEAMA,
LEAST(RADIANT_NAME,DIRE_NAME) TEAMB ,max(RADIANT_WIN) TEAMAWON,
SUM(RADIANT_WIN)-max(RADIANT_WIN) TEAMBWON,
SUM(RADIANT_WIN)-max(RADIANT_WIN) TEAMALOST,
SUM(RADIANT_WIN)-(SUM(RADIANT_WIN)-Max(RADIANT_WIN)) TEAMBLOST
FROM
(
   SELECT MA.SERIES_ID,MA.RADIANT_NAME,DIRE_NAME ,
   COUNT(RADIANT_WIN) RADIANT_WIN FROM MATCHES MA,SERIES_TYPE ST
   WHERE  MA.SERIES_TYPE=ST.TYPE 
   GROUP BY MA.SERIES_ID,MA.RADIANT_NAME,DIRE_NAME
) GROUP BY SERIES_ID,
  greatest(RADIANT_NAME,DIRE_NAME) ,least(RADIANT_NAME,DIRE_NAME);