Each GROUP BY expression must contain at least one column that is not an outer reference

Transact-SQL
1

Hi, I'm hoping someone can help me. I'm getting the error in the subject line when I try and run this code, I believe the problem is because I'm trying to group by a SUDO name rather than one in the table itself. However, if I group by "ClientProduct" rather than department I get multiple entries of the same name. Can anyone help?

Select Case When ClientProduct In (18,20) Then 'CMS'
When ClientProduct In (21,29) Then 'Import'
When ClientProduct in (28,36,37) Then 'Export'
When ClientProduct = 16 Then 'Legal'
Else 'Other' End as 'Department',
Count(*)
--Sum(Case When ClientProduct > '0' Then 1 Else 0 End) as "Volume"
From IRL_CollDesk Where (Status is NULL or Status = 'C')
Group By 'Department'

Results grouping by ClientProduct

Department (No column name)
Import 132
Legal 1615
Import 537
CMS 2
Other 28
Export 2156
Other 33
Export 4
CMS 4631

Desired Results

Department (No column name)
Import 668
Legal 1615
CMS 4633
Other 61
Export 2160
2 
Select Case When ClientProduct In (18,20) Then 'CMS'
When ClientProduct In (21,29) Then 'Import'
When ClientProduct in (28,36,37) Then 'Export'
When ClientProduct = 16 Then 'Legal'
Else 'Other' End as 'Department',
Count(*)
--Sum(Case When ClientProduct > '0' Then 1 Else 0 End) as "Volume"
From IRL_CollDesk 
Where (Status is NULL or Status = 'C')
Group By Case When ClientProduct In (18,20) Then 'CMS'
When ClientProduct In (21,29) Then 'Import'
When ClientProduct in (28,36,37) Then 'Export'
When ClientProduct = 16 Then 'Legal'
Else 'Other' End
3

image

use sqlteam
go
--sample data
create table #IRL_CollDesk(ClientProduct  int, Status char(1))

insert into #IRL_CollDesk
select column_id, 
       null
 from sys.all_columns
 where column_id between 16 and 37

;with src
as
(
Select Case When ClientProduct In (18,20) Then 'CMS'
When ClientProduct In (21,29) Then 'Import'
When ClientProduct in (28,36,37) Then 'Export'
When ClientProduct = 16 Then 'Legal'
Else 'Other' End as 'Department'
From #IRL_CollDesk 
Where (Status is NULL or Status = 'C')
) 

select Department,
       count(1)
  from src
  group by Department

drop table #IRL_CollDesk
4

I just had to group by the case. Thanks Scott much appreciated.