0

Say I have the following table, where a group of 3 people from 3 locations work together on a project that is scored.

|   | Score | Possible_Score | Loc_A | Loc_B | Loc_C |
|---|-------|----------------|-------|-------|-------|
| 1 | 80    | 100            | LA    | LA    | Bos   |
| 2 | 90    | 100            | LA    | Bos   | NYC   |
| 3 | 70    | 100            | NYC   | NYC   | LA    |

I want to get overall scores by location, where each project that has at least 1 person from that location contributes to the location's score. So, in this case, I'd want an output like:

|   | Location | Score | Poss_Score |
|---|----------|-------|------------|
| 1 | Bos      | 170   | 200        |
| 2 | LA       | 240   | 300        |
| 3 | NYC      | 160   | 200        |

How can I do this in SQL? I know how to group by location, but I don't know how to group by location when it can be from any of 3 columns. Thank you for the help!

3

A solution that work in most DBMS is to use a union:

 select loc_a as location, Score, Possible_Score from T
 union all
 select loc_b as location, Score, Possible_Score from T
 union all
 select loc_c as location, Score, Possible_Score from T

Now you can sum that up as:

select location, sum(score), sum(possible_score)
from (
     select loc_a as location, Score, Possible_Score from T
     union all
     select loc_b as location, Score, Possible_Score from T
     union all
     select loc_c as location, Score, Possible_Score from T
) as TT
group by location;

If your DBMS supports LATERAL JOIN you can do something like:

select x.location, sum(x.score), sum(x.possible_score)
from T
cross join lateral (
    values (T.loc_a, T.score, T.possible_score)
         , (T.loc_b, T.score, T.possible_score)
         , (T.loc_c, T.score, T.possible_score)
) X (location, score, possible_score)
group by x.location;

LATERAL makes it possible to reference T in X, so we can use X to normalize location.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.