SQL Server 2008 with group by and case. Group by is not working-CodePudding

Could someone please help me to solve my problem with group by

I have this SQL code (in SQL Server 2008)

SELECT DISTINCT
    '2' AS report,
    DepartmentName,
    CASE 
        WHEN @GroupBy = 'Division' THEN DivisionName
        WHEN @GroupBy = 'Department' THEN '' 
    END AS DivisionName,
    Rank,
    CASE 
        WHEN DivisionName = '' THEN NULL 
        ELSE AVG(Amount) 
    END AS Amount,
FROM
    #Report
GROUP BY
    DepartmentName,
    DivisionName,
    Rank

It's not grouping by division (I'm getting 3 Category1 because they belong to 3 different divisions). Do you know why?

2   Group   0   Category1
2   Group   0   Category1               
2   Group   0   Category1               
2   Group   0   Category2               
2   Group   0   Category3                   
2   Group   0   Category4       
2   Group   0   Category15

Thanks a lot in advance!

CodePudding user response：

The truth is, we can't answer if we don't see source data. I'd hazard a guess it is what Dale mentions in the comments:

If your 3 Category1 have different [DivisionName]s in the original table, they will come back as separate rows. Group By is "executed" before the select, so if you want to base your new data off a calculation, you should do it in a derived table:

SELECT 
    '2' AS report,
    DepartmentName,
    derived_table.DivisionName, -- alias added
    Rank,
    CASE 
        WHEN derived_table.DivisionName = '' THEN NULL 
        ELSE AVG(Amount) 
    END AS Amount,
FROM
    #Report
    cross apply
    (
        select  CASE 
                    WHEN @GroupBy = 'Division' THEN DivisionName
                    WHEN @GroupBy = 'Department' THEN '' 
                END AS DivisionName
    ) as derived_table
GROUP BY
    DepartmentName,
    derived_table.DivisionName,
    Rank