分组统计并计算每组数量sql

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

有字段A 和B
比如数据如下
A B
1 2
1 3
1 4
2 2
2 3

统计出的sql结果：

A count

1 3

2 2

select a,count(b) from t group by a;
--或count(b) over (partition by a)

如统计一级会计科目数量的sql：

select fo,count(dispname)from(
select substr(subjcode,1,4) fo,bd_accsubj.dispname,
bd_accsubj.pk_accsubj, bd_accsubj.pk_glorgbook
from bd_accsubj
where pk_glorgbook in(select pk_glorgbook from bd_glorgbook where glorgbookcode= '010502-0001' )
order by dispname)
group by fo order by fo

方法二 partition 这个时候后面不需要group by 但是需要distinct fo 否则见下图

select id,count(*) over(partition by pro_id) from sal;
以pro_id分组，统计分组后每个pro_id的记录总数及对应的id；
类似还有count(*) over(order by ……)、sum(amount) over(partition by ……)等，略有区别，挺有意思，有兴趣可以小研究下

select distinct fo,count(dispname) over( partition by fo) num1 from
(
select substr(subjcode,1,4) fo,bd_accsubj.dispname,
bd_accsubj.pk_accsubj, bd_accsubj.pk_glorgbook
from bd_accsubj
where pk_glorgbook in(select pk_glorgbook from bd_glorgbook where glorgbookcode= '010502-0001' )
order by dispname)
order by fo

如果group by fo ，这里不能group by，总结：partition是group by的一种形式，group 不需要重复

不加distinct fo

更新2012-11-9 19:12：增加一列显示一级科目名称需要外面嵌套两个select

select fo,
(select subjname
from bd_accsubj
where subjcode = fo
and pk_glorgbook = '0001E1100000000000MX') aa,num1
from (select fo, count(dispname)num1
from (select substr(subjcode, 1, 4) fo,
bd_accsubj.dispname,
bd_accsubj.pk_accsubj,
bd_accsubj.pk_glorgbook
from bd_accsubj
where pk_glorgbook in
(select pk_glorgbook
from bd_glorgbook
where glorgbookcode = '010101-0001')
order by dispname)
group by fo)
order by fo