SQL Case Case Statement и функции агрегирования

У меня есть следующие данные таблицы:

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

Я ищу следующий результат:

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

Я хочу сгруппировать по идентификатору цитаты и вычитаемому плану и должен суммировать по столбцу возраста, но я не уверен, как это сделать, вот моя попытка:

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]

Результат:

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

Как бы я суммировал идентификатор возраста, чтобы дать мне результат:

Quote ID Deductible Plan        Age 65      Age 85+
1381    $750 Deductible Plan    10          10
1371    $150 Deductible Plan    10          10
Источник

2 ответа

Решение

Применить дела к [Number of Members] а не SUM([Number of Members]) и не группировать по [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]
;
Источник 
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
Источник
Другие вопросы по тегам