SQL Get SUM using group by, but sum contents of one of two columns conditionally

user_5813620 0
Viewed 24

Using SQL Server, I need to summarize records in a table and get one summed value. My problem is that I need to sum values from one of two columns in each record, based on the condition in a third column.

Example: Here is a table:

Station   Location  Contact ColToSum    AltColToSum ColToUse
Station 1   West    Smith      4             10        1
Station 1   West    Smith      0             10        2
Station 1   West    Smith      2             10        1
Station 1   West    Smith      7             10        1
Station 2   North   Jones      5             10        1
Station 2   North   Jones      0             10        2
Station 2   North   Jones      0             10        2
Station 3   South   Brown      5             10        1
Station 3   South   Brown      0             10        2
Station 3   South   Brown      0             10        2
Station 3   South   Brown      1             10        1

The rightmost column contains the column that needs to be added to the sum, either ColToSum or AltColToSum. If ColToUse = 1, ColToSum value is added to the sum; if ColToUse = 2, AltColToSum is added to sum. The data are grouped to get a summary, like this:

Station 1   West    Smith   23
Station 2   North   Jones   25
Station 3   South   Brown   26

I've been playing with the CASE statement and GROUP BY clause, but I can't seem to get the result that I want. I also tried to use PARTITION, but I'm not very skilled with that yet. Any help would be appreciated.

aggregate-functions group-by sum sql
1 Answers

Just use conditional aggregation:

select station, location, contact,
       sum(case when coltouse = 1 then coltosum
                when coltouse = 2 then altcoltosum
           end) as sum
from t
group by station, location, contact;
user_11440350
Related