CODEWITHSUNDEEP
sql-servert-sqlgroup-by
Dan
Dan

Reputation: 45752

conditional sum of one column based on another and then all grouped by a third

Assume a table like this:

| A | B | C |
-------------
| a | 1 | 1 |
| a | 4 | 1 |
| b | 2 | 1 |
| b | 6 | 3 |
| a | 4 | 6 |
| b | 2 | 7 |

I want to get the sum of B for different bands of C grouped by A. I can do it by making a new column per band in an inner query that has ones when I want to sum values and zeros where I don't like this:

SELECT A, SUM(B*band_1) as band_1, SUM(B*band_2) as band_2
FROM (SELECT A,
             B,
             CASE 
               WHEN C <= 5 THEN 1
               ELSE 0
             END as band_1,
             CASE 
               WHEN C > 5 and C <= 10 THEN 1
               ELSE 0
             END as band_2
       FROM MyTable) as T
GROUP BY A

Which works. But when I have a lot of bands, this query will grow quite unwieldy. Is there some clever method to do this better? Maybe using PIVOT or dynamic SQL or even OLAP or something?

Upvotes: 1

Views: 2903

Answers (1)

EricZ
EricZ

Reputation: 6205

I'm not sure why you need a sub-query, if you don't have a pattern for bands, maybe try this way

SELECT  A,
    SUM(CASE WHEN C <= 5 THEN B ELSE 0 END) as band_1,
    SUM(CASE WHEN C > 5 and C <= 10 THEN B ELSE 0 END) as band_2
FROM MyTable
GROUP BY A

Upvotes: 3

Related Questions