SQL group by multiple columns

group by

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!

Best Answer

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.

Related Solutions

Sql-server – How to select multiple columns but only group by one

In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. I've blogged about this in detail here. So you have two options:

Add the additional columns to the GROUP BY clause:

GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName]

Add some aggregate function on the relevant columns:

SELECT Rls.RoleName, MAX(Pro.[FirstName]), MAX(Pro.[LastName])

The second solution is mostly a workaround and an indication that you should fix something more general with your query.

Mysql – GROUP BY multiple columns

Personally, I hate having a CASE in the middle ofSQL. It looks and feels clumsy.

You should think of this in terms of Dynamic SQL.

Perhaps something like this

CREATE PROCEDURE `uic_get_stats_transactions_100`(
    p_channel_id int unsigned,
    p_startdate int unsigned,
    p_enddate int unsigned,
    p_increment varchar(10)
)
BEGIN
    SET @p_inc = p_increment;
    SET @p_inclist = 'days,months,years';
    SET @ndx = FIND_IN_SET(@p_inc,@p_inclist);
    SET @groupby =
        IF(
            @ndx=0,'datestamp',
            ELT(
                @ndx,
                'datestamp',
                'DATE_FORMAT(datestamp,''%Y-%m-01'')',
                'DATE_FORMAT(datestamp,''%Y'')'
            )
        );
    SET @sql = 'select datestamp,trans_type,trans_status,';
    SET @sql = CONCAT(@sql,'sum(base_amount) ''total_amount'',');
    SET @sql = CONCAT(@sql,'sum(trans_fees) ''total_fees'',');
    SET @sql = CONCAT(@sql,'sum(volume) ''total_volume'' ');
    SET @sql = CONCAT(@sql,'from channel_stats where ');
    SET @sql = CONCAT(@sql,'channel_id = p_channel_id and ');
    SET @sql = CONCAT(@sql,'udatestamp >= p_startdate and ');
    SET @sql = CONCAT(@sql,'udatestamp <= p_enddate ');
    SET @sql = CONCAT(@sql,'group by ',@groupby);
    SET @sql = CONCAT(@sql,',trans_type, trans_status');
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
END

If p_increment is not days, months, or years, the default is days

Best Answer

Related Solutions

Sql-server – How to select multiple columns but only group by one

Mysql – GROUP BY multiple columns

Related Question