group by in sql combining values [closed]

Question

I have a table (sql 2008) with A,B,C,D,E values in col1 Is there a way to get counts grouped by col1 so that result returned will be

A - #
B - #
other - #

Thank you

Answer 1

Repeating the CASE expression works, but I find it a little less tedious to only perform that expression once. Plans are identical.

;WITH x AS 
(
  SELECT Name = CASE WHEN Name IN ('A','B') THEN Name 
  ELSE 'Other' END
  FROM dbo.YourTable
)
SELECT Name, COUNT(*) FROM x 
GROUP BY Name;

If ordering is important (e.g. Other should be the last row in the result, even if other names come after it alphabetically), then you can say:

ORDER BY CASE WHEN Name = 'Other' THEN 1 ELSE 0 END, Name;

Answer 2

select
   case when col1 in ('a','b') then col1 else 'other' end,
   count(*)
from tab
group by case when col1 in ('a','b') then col1 else 'other' end