Make table out of different columns and assign values to add them up-CodePudding

I want this:

Winner_R | Winner_PV | Loser_PV
---------|-----------|----------
Team_A   | NULL      | NULL
---------|-----------|----------
NULL     | Team_B    | Team_C
---------|-----------|----------
Team_C   | NULL      | NULL

where a win equals 3 points, a win in PV equals 2 points and a lose in PV equals to 1 point,

to show like this:

Team   | Points
-------|-------
Team_A | 3
-------|-------
Team_B | 2
-------|-------
Team_C | 4

I just can't figure out how to connect the values with SQL-Statements! Any help is appreciated :)

CodePudding user response：

You can do:

select team, sum(points) as points
from (
  select winner_r as team, count(*) * 3 as points from t group by winner_r
  union all select winner_pv, count(*) * 2 from t group by winner_pv
  union all select loser_pv, count(*) from t group by loser_pv
) x
where team is not null
group by team

Alternatively, you can filter out rows first and aggregate at the end, as in:

select team, sum(points) as points
from (
  select winner_r as team, 3 as points from t where winner_r is not null
  union all select winner_pv, 2 from t where winner_pv is not null
  union all select loser_pv, 1 from t where loser_pv is not null
) x
group by team