You work for a software company that released a 2-player game and you need to tally the scores.
Given the following table , write a SQL statement to determine the reciprocals and calculate their aggregate score. In the data below, players 3 and 4 have two valid entries, but their scores need to be aggregated together.
Source:
with game as (
select 1 playera, 2 playerb, 124 score from dual
union all
select 3 playera, 4 playerb, 112 score from dual
union all
select 4 playera, 3 playerb, 32 score from dual)
SELECT
playera,
playerb,
score
FROM
( SELECT
CASE
WHEN playera >= playerb THEN
playerb
ELSE
playera
END playera,
CASE
WHEN playera >= playerb THEN
playera
ELSE
playerb
END playerb, SUM(score) AS score
FROM game
GROUP BY
CASE WHEN playera >= playerb THEN playerb ELSE playera END,
CASE WHEN playera >= playerb THEN playera ELSE playerb END
);
No comments:
Post a Comment