sql 分类汇总查询语句

Question

比如:

id dept pay zt
———————————–
1 办公室 20.00 01
2 局领导 10.00 02
3 办公室 40.00 03
4 局领导 10.00 01
5 办公室 50.00 01
6 局领导 10.00 02
7 办公室 20.00 02
8 局领导 10.00 02
……
我现在想统计01、02两种状态的数量和pay合计。所有状态的数量
显示显示效果如下:
序号 部门 合计 个数（01状态） 个数（02状态） 总数
————————————————————————————————
1 办公室 90.00 2 1 4
2 局领导 50.00 1 3 4

Answer 1

select min(id) as 序号,
max(dept) as 部门,
sum(pay) as 合计,
sum(case zt when '01' then 1 else 0 end) as 个数01状态，
sum(case zt when '02' then 1 else 0 end) as 个数02状态，
count(zt) as 总数
from 表名
group by dept

这段代码是sqlserver和oracel通用，其中“表名”的地方，换成你的表名

喔看掉了这个条件：我现在想统计01、02两种状态的数量和pay合计

还是 zjwssg提醒，但最后两个sum中when后面，建议还是加单引号吧

把上面的代码改为这样应该没问题了
select min(id) as 序号,
max(dept) as 部门,
sum(case when zt='01' OR zt='02' then pay else 0 end) as 合计,
sum(case zt when '01' then 1 else 0 end) as 个数01状态，
sum(case zt when '02' then 1 else 0 end) as 个数02状态，
count(zt) as 总数
from 表名
group by dept

Answer 2

醉含笑的很牛，不过SUM（pay)有点需要改动

最终完美版：
select min(id) as 序号,
max(dept) as 部门,
sum(case when zt='01' OR zt='02' then pay else 0 end) as 合计,
sum(case zt when 01 then 1 else 0 end) as 个数01状态,
sum(case zt when 02 then 1 else 0 end) as 个数02状态,
count(zt) as 总数
from aac
group by dept

Answer 3

select
ROW_NUMBER() over(order by a.dept) 序号,
a.dept 部门,
a.合计,
b.个数01,
c.个数02,
d.总数
from

(select dept,SUM(pay) 合计 from t where zt='01' or zt='02' group by dept) a,
(select dept,COUNT(pay) 个数01 from t where zt='01' group by dept) b,
(select dept,COUNT(pay) 个数02 from t where zt='02' group by dept) c,
(select dept,COUNT(pay) 总数 from t group by dept) d
where a.dept=b.dept and b.dept=c.dept and c.dept=d.dept

参照楼上的写法,改进一下有:
select
ROW_NUMBER() over(order by dept) 序号,
dept as 部门,
sum(case when zt='01' OR zt='02' then pay else 0 end) 合计,
sum(case when zt='01' then 1 else 0 end) as 个数01状态,
sum(case when zt='02' then 1 else 0 end) as 个数02状态,
count(*) as 总数
from t
group by dept

Answer 4

create table tb (id int,dept varchar(10),pay int,zt int)
insert tb select 1,'办公室',20,1
union all select 2,'局领导',10,2
union all select 3,'办公室',40,3
union all select 4,'局领导',10,1
union all select 5,'办公室',50,1
union all select 6,'局领导',10,2
union all select 7,'办公室',20,2
union all select 8,'局领导',10,2

select identity(int,1,1) as 序号,
dept as 部门,
sum(case when zt='01' OR zt='02' then pay else 0 end) 合计,
sum(case when zt='1' then 1 else 0 end) 个数01状态,
sum(case when zt='2' then 1 else 0 end) 个数02状态,
count(*) as 总数 into #temp from tb group by dept

select * from #temp

Answer 5

如果你用的是sql server可以：
select 序号=identity(int,1,1),dept as 部门,sun(pay) as 合计,sum(case when zt='01' then 1 else 0 end) as 个数01状态,sum(case when zt='02' then 1 else 0 end) as 个数02状态,count(*) as 总数 into #tmp_total from yourtablename group by dept

select * from #tmp_total 就得到你要的效果了

你要说是在什么数据库下，数据库不同写法也是有一定差别的