T-SQL Conditional SUM operation

Question

I'm looking for a clean, concise way to perform a SUM operation conditionally. I know this gotta be fairly straight-forward but I'm unsure of how to proceed. To demonstare what I mean, take a look at this...

    DECLARE @Test TABLE
(
    LevelID int,
    DataFieldID INT,
    RangeID INT,
    Value FLOAT
)

INSERT INTO @Test VALUES (22, 6, 117, 100)
INSERT INTO @Test VALUES (22, 6, 122, 100)
INSERT INTO @Test VALUES (22, 6, 126, 100)

INSERT INTO @Test VALUES (22, 7, 117, 100)
INSERT INTO @Test VALUES (22, 7, 122, 100)
INSERT INTO @Test VALUES (22, 7, 126, 100)

INSERT INTO @Test VALUES (23, 6, 117, 100)
INSERT INTO @Test VALUES (23, 6, 122, 100)
INSERT INTO @Test VALUES (23, 6, 126, 100)

INSERT INTO @Test VALUES (23, 7, 117, 100)
INSERT INTO @Test VALUES (23, 7, 122, 100)
INSERT INTO @Test VALUES (23, 7, 126, 100)


SELECT
    LevelID, 
    RangeID, 
    SUM(Value[where DataFieldID = 6]) / SUM(Value[where DataFieldID = 7])
FROM
    @Test
GROUP BY LevelID, RangeID

Any idea what would be the best way to achieve the above SUM operation?

Matt Whitfield · Accepted Answer · 2013-11-11 12:51:49Z

5

You can use CASE WHEN within the aggregate:

SELECT
    LevelID, 
    RangeID, 
    SUM(CASE WHEN DataFieldID = 6 THEN Value ELSE 0 END) / 
    SUM(CASE WHEN DataFieldID = 7 THEN Value ELSE 0 END)
FROM
    @Test
GROUP BY LevelID, RangeID

answered Nov 11, 2013 at 12:51

Matt Whitfield

6,6343 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:12:40Z

0

I was also going to suggest the same CASE as @Matt suggested, but I figured you might want the total across all DataFieldId, as in...? maybe I've misunderstood, I will delete if that's the case...

SELECT  LevelID ,
        RangeID ,
        a.total
FROM    @Test
        INNER JOIN ( SELECT SUM(Value) total ,
                            DataFieldID
                     FROM   @Test
                     GROUP BY DataFieldID
                   ) a ON [@Test].DataFieldID = a.DataFieldID
GROUP BY LevelID ,
        RangeID ,
        a.total

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Nov 11, 2013 at 12:56

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

2 Comments

Damien_The_Unbeliever Over a year ago

I know there's only two answers at the moment, but you should avoid using positional language to refer to answers on stack overflow - in (30 seconds/a day/a week/a year), the answer that is "above" may no longer be the answer you were attempting to refer to, due to voting, deletions, etc. It's better to include a link to the answer you want to refer to (use the share option at the bottom to get a link) (I usually also put the name of the author of the answer)

Christian Phillips Over a year ago

@Damien_The_Unbeliever, thanks for the tip, I have updated the post. Although, I will probably delete the post soon as my interpretation looks incorrect...I'll wait for the OP to confirm though.

Collectives™ on Stack Overflow

T-SQL Conditional SUM operation

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related