SQL Server Case Statement and Aggregation functions

Question

I have the following table data:

SELECT [Quote ID], [Deductible Plan], [Age ID], [Number of Members] 
FROM finalResult_001

1381    $750 Deductible Plan    Age 65      10
1381    $750 Deductible Plan    Age 85+     10
1371    $150 Deductible Plan    Age 65      10
1371    $150 Deductible Plan    Age 85+     10

I am looking for the following result:

Quote ID Deductible Plan       Age 65      Age 85+
1381    $750 Deductible Plan    10          10
1371    $150 Deductible Plan    10          10

I want to Group by Quote ID and Deductible Plan and should sum by Age ID column, however I am not sure how to do it, here is my try:

SELECT [Quote ID], [Deductible Plan],

case when [Age ID] = 'Age 65' THEN SUM([Number of Members]) else 0 END AS [Age 65],   
case when [Age ID] = 'Age 85+' THEN SUM([Number of Members]) else 0 END AS [Age 85+]

FROM finalResult_001
GROUP BY [Quote ID], [Age ID], [Deductible Plan]

THe result:

Quote ID Deductible Plan        Age 65      Age 85+
1381    $750 Deductible Plan    0           10
1381    $750 Deductible Plan    10          0
1371    $150 Deductible Plan    0           10
1371    $150 Deductible Plan    10          0

how would I sum the Age ID to give me the resule:

Quote ID Deductible Plan        Age 65      Age 85+
1381    $750 Deductible Plan    10          10
1371    $150 Deductible Plan    10          10

score 4 · Accepted Answer · answered Aug 04 '14 at 06:16

Apply the CASEs to [Number of Members] rather than to SUM([Number of Members]) and do not group by [Age ID]:

SELECT
  [Quote ID],
  [Deductible Plan],
  SUM(case when [Age ID] = 'Age 65'  THEN [Number of Members] else 0 END) AS [Age 65],   
  SUM(case when [Age ID] = 'Age 85+' THEN [Number of Members] else 0 END) AS [Age 85+]
FROM dbo.finalResult_001
GROUP BY [Quote ID], [Deductible Plan]
;

score 1 · Answer 2 · edited Aug 05 '14 at 10:00

DECLARE @t Table (QuoteID INT,Deductible VARCHAR(100),Age INT,AgeID INT)

INSERT INTO @t(QuoteID,Deductible,Age,AgeID)values (1381,'$750 Deductible Plan',0,10)
INSERT INTO @t(QuoteID,Deductible,Age,AgeID)values (1381,'$150 Deductible Plan',10,0)
INSERT INTO @t(QuoteID,Deductible,Age,AgeID)values (1371,'$750 Deductible Plan',0,10)
INSERT INTO @t(QuoteID,Deductible,Age,AgeID)values (1371,'$150 Deductible Plan',10,0)

;WITH CTE
AS (
    SELECT DISTINCT t.QuoteID
        ,MAX(tt.Age) AA
        ,MAX(ttt.Ageid) AAA
        ,ROW_NUMBER() OVER (
            PARTITION BY t.Deductible ORDER BY t.Deductible
            ) RN
        ,t.Deductible
    FROM @t t
    INNER JOIN (
        SELECT Age
            ,QuoteID
        FROM @t
        GROUP BY QuoteID
            ,Deductible
            ,Age
            ,AgeID
        ) tt ON tt.QuoteID = t.QuoteID
    INNER JOIN (
        SELECT AgeID
            ,QuoteID
            ,Deductible
        FROM @t
        GROUP BY QuoteID
            ,Deductible
            ,Age
            ,AgeID
        ) ttt ON ttt.QuoteID = t.QuoteID
    GROUP BY t.QuoteID
        ,t.Deductible
    )
SELECT C.QuoteID
    ,C.Deductible
    ,C.AA AGE
    ,C.AAA Age1
FROM Cte C
WHERE RN = 1

SQL Server Case Statement and Aggregation functions

2 Answers2