GROUP BY with CASE - tsql

Question

I have a select which looks like the following:

SELECT 
        ReportingDate
    ,   PortfolioID
    ,   PortfolioNme

    ,   CASE
            WHEN @ReportType = 'GeoCountry' THEN Infoportal.dbo.fn_Generic_ProperCase(Country)
            WHEN @ReportType = 'GeoEquity'  THEN Region
        END                 AS Country

    ,   RANK() OVER (PARTITION BY PortfolioID ORDER BY SUM(Percentage) DESC, CASE WHEN @ReportType = 'GeoCountry' THEN Country WHEN @ReportType = 'GeoEquity' THEN Region END) AS [Rank]
    ,   SUM(Percentage)     AS [Weight]

FROM @Worktable as WT

WHERE WT.IssueType1 <> '010' AND WT.IssueType2 <> '055'

GROUP BY    WT.ReportingDate
        ,   WT.PortfolioID
        ,   WT.PortfolioNme
        ,   CASE
                WHEN @ReportType = 'GeoCountry' THEN WT.Country
                WHEN @ReportType = 'GeoEquity'  THEN WT.Region
            END     

What i'm trying to do is group by country or region depending upon the @ReportType and show the sum of the percentage and a rank.

However I keep getting the error:

Msg 8120, Level 16, State 1, Line 349
Column '@Worktable.Country' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 350
Column '@Worktable.Region' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is so easy and it is really annoying me. Help would be greatly appreciated.

Thanks

Answer 1

I believe a CTE would simplfy what you're trying to do:

;WITH WT AS (
    SELECT 
        ReportingDate
    ,   PortfolioID
    ,   PortfolioNme

    ,   CASE
            WHEN @ReportType = 'GeoCountry' 
              THEN Infoportal.dbo.fn_Generic_ProperCase(Country)
            WHEN @ReportType = 'GeoEquity'  
              THEN Region
        END AS Country
    ,   Percentage
FROM @Worktable
WHERE IssueType1 <> '010' AND IssueType2 <> '055'
)
SELECT 
        ReportingDate
    ,   PortfolioID
    ,   PortfolioNme
    ,   Country
    ,   RANK() OVER (
          PARTITION BY PortfolioID 
          ORDER BY SUM(Percentage) DESC, Country) AS [Rank]
    ,   SUM(Percentage) AS [Weight]
FROM WT
GROUP BY
        ReportingDate
    ,   PortfolioID
    ,   PortfolioNme
    ,   Country