Having "___ in (select distinct ___ from #temp)" in Case statement

Question

Hi,

I'm trying to achieve this

select
   case
      when Org_CD = '1111' or Org_CD in (select distinct New_Org_CD from #temp) then 'International'
   end as 'Organisation',
count(*)
from #AnotherTempTable
group by
   case
      when Org_CD = '1111' or Org_CD in (select distinct New_Org_CD from #temp) then 'International'
   end 

I received this error:

Column '#AnotherTempTable.Org_Cd' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is it because I cannot use "in" keyword inside the case statements? If yes, any known workarounds would be more than helpful!

Answer 1

I'd try this...

select
    Org_CD, count(*)
from
    #AnotherTempTable A
    JOIN
    (select distinct New_Org_CD from #temp UNION SELECT '1111') T ON A.Org_CD = T.New_Org_CD
group by
   Org_CD

You can't have an inline IN like this (CASE + aggregate)

If this is not OK, please give sample data and output

Answer 2

I solved it with a variation of gbn's solution using 'Union'. Thanks all.