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

December 13, 2021 Off By Glespynorson

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!