CODEWITHSUNDEEP
sqlsql-servergroup-by
Kristy Yu
Kristy Yu

Reputation: 31

Group by multiple values in one column

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?

Upvotes: 3

Views: 5458

Answers (4)

jannj
jannj

Reputation: 1

You don't need to write the CASE-Statement twice, like jose_bacoy did. You can refer to the name you gave it in the GROUP BY and ORDER BY clause.

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

Upvotes: 0

Ricky McMaster
Ricky McMaster

Reputation: 4647

You can just use a CASE statement.

SELECT
  CASE WHEN Status IN ('a', 'b', 'd')
    THEN 'I'
  WHEN Status IN ('e', 'f')
    THEN 'II'
  END AS "Status Group",
  count(*) as Count
FROM Table
GROUP BY 1
ORDER BY 1
;

Upvotes: 3

jose_bacoy
jose_bacoy

Reputation: 12684

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

Upvotes: 3

Ven
Ven

Reputation: 2014

you can use group by with case 

declare @table table ( client INT, STATUS Varchar(10))
insert into @table
select 1  , 'a' union all
select 2 ,  'b' union all
select 3  , 'a' union all
select 4  , 'd' union all
select 5  , 'd' union all
select 6  , 'a' union all
select 7  , 'e' union all
select 8  , 'b' union all
select 9  , 'e' union all
select 10 , 'f'

Query:

select clientgroup, count(*) Count from
(
select case when status in ( 'a','b','d') then 'I' else 'II' end clientgroup from @table 
) x group by clientgroup

Upvotes: 0

Related Questions