我的数据表看起来像这样:
客户端 状态 1 a 2 b 3 a 4 d 5 d 6 a 7 e 8 b 9 e 10 f
假设我想按状态对表进行分组,但是我想将状态 a,b,d
(状态 I
)作为一组,e,f
(状态 II
)作为另一组
最终我希望它看起来像这样
状态 计数 I 7 II 3
我应该如何编写查询?
我的数据表看起来像这样:
客户端 状态 1 a 2 b 3 a 4 d 5 d 6 a 7 e 8 b 9 e 10 f
假设我想按状态对表进行分组,但是我想将状态 a,b,d
(状态 I
)作为一组,e,f
(状态 II
)作为另一组
最终我希望它看起来像这样
状态 计数 I 7 II 3
我应该如何编写查询?
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
;
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
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'
查询:
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
CASE
语句。你可以在GROUP BY
和ORDER BY
子句中引用你给它的名称。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