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

Glespynorson

3 years ago

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!