sql server: case statement in group by

Question

I have this query.

   Select a."AreaBlkType",Case a."AreaBlkType"
when 3 then 'Others'
else ( case a."CropType"
        when 1 then 'Oil Palm'
        when 2 then 'Rubber'
        else 'Other Crop'
        end
         )
end [Crop] 
    from Table1 a 
    group by 
    case a."AreaBlkType"
when 3 then 'Others'
else ( case a."CropType"
        when '1' then 'Oil Palm'
        when '2' then 'Rubber'
        when '3' then 'Other Crop'
        end )
end, 
a."AreaBlkType" 

But i got an error for this. : CropType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Is my sql correct?

Answer 1

Why do you need the GROUP BY? Are you expecting duplicate records and only want distinct one?

In either case, your GROUP BY must contain the identical CASE statement, so:

Select
   Case a."AreaBlkType"
      when 3 then 'Others'
      else
         case a."CropType"
            when 1 then 'Oil Palm'
            when 2 then 'Rubber'
            else 'Other Crop'
         end
   end [Crop] 
 from Table1 a 
group by 
   Case a."AreaBlkType"
      when 3 then 'Others'
      else
         case a."CropType"
            when 1 then 'Oil Palm'
            when 2 then 'Rubber'
            else 'Other Crop'
         end
   end