Group by multiple values in one column

Question

My data table looks like this:

client   Status
   1       a
   2       b
   3       a
   4       d
   5       d
   6       a
   7       e
   8       b
   9       e
  10       f

Say I want to group by the table by status, but instead, I want group status a,b,d (status I) as one group and e,f (status II) as another group

I want it looks like this eventually

status Count
   I      7
  II      3

How should I write the query?

Answer 1

You can run the grouping and count into one statement.

SELECT
  CASE WHEN Status IN ('a', 'b', 'd') THEN 'I' ELSE 'II' END AS Status,
  count(*) as count
FROM Table1
GROUP BY  (CASE WHEN Status IN ('a', 'b', 'd') THEN 'I' ELSE 'II' END)
ORDER BY Status

Result:
Status  count
 I      7
II      3