Using column in CASE with COUNT without having it in group by

Question

SELECT 
    COUNT([P].[pkPortfolioId]) AS [OutcomeNotSubmitted],
    [CG].[GroupName]
FROM
    opp.Portfolio AS [P]
INNER JOIN 
   [vendor].[CustomerGroup] AS [CG] ON [P].[fkCustomerGroupId] = [CG].[pkCustomerGroupId]
WHERE 
    fkOutcomeId IS NULL
GROUP BY 
    [CG].[GroupName]

This query works fine but there are multiple outcomes as shown below.In order for this query to work I need to duplicate this query to achieve desired results. Plus there are a lot of joins.Bear in mind this is the trimmed query for example.

I want to achieve this result:

NAME    WIN    PartialWin    Loss    OutcomeNotSubmitted
---------------------------------------------------------
Chain    1        0           1                 0

Below this my attempt to achieve this in one query

SELECT 
    CASE
        WHEN [P].[fkOutcomeId] = 1 THEN COUNT([P].[pkPortfolioId])
    END AS [Win],
    CASE
        WHEN [P].[fkOutcomeId] = 2 THEN COUNT([P].[pkPortfolioId])
    END AS [PartialWin],
    CASE
        WHEN [P].[fkOutcomeId] = 3 THEN COUNT([P].[pkPortfolioId])
    END AS [Loss],
    CASE
        WHEN [P].[fkOutcomeId] IS NULL THEN COUNT([P].[pkPortfolioId])
    END AS [OutcomeNotSubmitted],
    [CG].[GroupName]
FROM 
    opp.Portfolio AS [P]
INNER JOIN 
    [vendor].[CustomerGroup] AS [CG] ON [P].[fkCustomerGroupId] = [CG].[pkCustomerGroupId]
GROUP BY 
    [CG].[GroupName]

Error:

Column 'opp.Portfolio.fkOutcomeId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

score 4 · Accepted Answer · answered Aug 11 '17 at 20:33

Wrong order. The case should be the argument to the aggregation function. Assuming the portfolio id is never NULL, I would use SUM():

SELECT SUM(CASE WHEN [P].[fkOutcomeId] = 1 THEN 1 ELSE 0 END) AS Win,
       SUM(CASE WHEN [P].[fkOutcomeId] = 2 THEN 1 ELSE 0 END) AS PartialWin,
       SUM(CASE WHEN [P].[fkOutcomeId] = 3 THEN 1 ELSE 0 END) AS Loss,
       SUM(CASE WHEN [P].[fkOutcomeId] IS NULL THEN 1 ELSE 0 END) AS OutcomeNotSubmitted,
       [CG].[GroupName]
FROM opp.Portfolio [P] INNER JOIN
     [vendor].[CustomerGroup] [CG]
     ON [P].[fkCustomerGroupId] = [CG].[pkCustomerGroupId]
GROUP BY [CG].[GroupName];

I was in the middle of typing the same thing when this answer popped up. This is it. — Nick, Aug 11 '17 at 20:34

score 0 · Answer 2 · answered Aug 11 '17 at 21:16

Above answer is correct if you do not include any more joins then already shown above.

As more joins are added, SUM does not fulfill requirement to count number of pkPortfolioId anymore.

SELECT COUNT(DISTINCT(CASE WHEN [P].[fkOutcomeId] = 1 THEN [P].[pkPortfolioId] ELSE NULL END)) AS Win,
       COUNT(DISTINCT(CASE WHEN [P].[fkOutcomeId] = 2 THEN [P].[pkPortfolioId] ELSE NULL END)) AS PartialWin,
       COUNT(DISTINCT(CASE WHEN [P].[fkOutcomeId] = 3 THEN [P].[pkPortfolioId] ELSE NULL END)) AS Loss,
       COUNT(DISTINCT(CASE WHEN [P].[fkOutcomeId] IS NULL THEN [P].[pkPortfolioId] ELSE NULL END)) AS OutcomeNotSubmitted,
   [CG].[GroupName] AS [Name]
FROM [opp].[Portfolio] AS [P]
INNER JOIN [vendor].[CustomerGroup] AS  [CG] ON [P].[fkCustomerGroupId] = [CG].[pkCustomerGroupId]
GROUP BY [CG].[GroupName]

Using column in CASE with COUNT without having it in group by

2 Answers2