oracle聚合函数中如何设置条件:原始语句: select ForgNum,count(FempNum),sum(FSAL200) from cmpdata group by forgnum 这里的count(fempNum)想设置一个条件,就是FSAL200>0才计数进去 怎么修改语句?.
或者直接计数count(FSAL200),要求条件是FSAL200值大于0...oracle有类似于excel中countif()的函数吗?
或者直接计数count(FSAL200),要求条件是FSAL200值大于0...oracle有类似于excel中countif()的函数吗?
因为我要计出FSAL200、FSAL100、FSAL308等很多薪酬科目中数值大于0的计数值。
select ForgNum,count(FSAL200),sum(FSAL200),count(FSAL100),sum(FSAL100),count(FSAL308),sum(FSAL308) from cmpdata group by forgnum 这样加where显然不行的...我不仅做一个计数;
对于null,count是不计入的。
SELECT FORGNUM,
SUM(CASE WHEN FSAL200>0 THEN 1 ELSE 0 END),
SUM(CASE WHEN FSAL200>0 THEN FSAL200 ELSE 0 END),
COUNT(CASE WHEN FSAL100>0 THEN 1 ELSE 0 END),
SUM(CASE WHEN FSAL100>0 THEN FSAL200 ELSE 0 END),
COUNT(CASE WHEN FSAL308>0 THEN 1 ELSE 0 END),
SUM(CASE WHEN FSAL308>0 THEN FSAL200 ELSE 0 END)
FROM CMPDATA
GROUP BY FORGNUM
;
SELECT FORGNUM,
SUM(CASE WHEN FSAL200>0 THEN 1 ELSE 0 END),
SUM(CASE WHEN FSAL200>0 THEN FSAL200 ELSE 0 END),
COUNT(CASE WHEN FSAL100>0 THEN 1 ELSE 0 END),
SUM(CASE WHEN FSAL100>0 THEN FSAL100 ELSE 0 END),
COUNT(CASE WHEN FSAL308>0 THEN 1 ELSE 0 END),
SUM(CASE WHEN FSAL308>0 THEN FSAL308 ELSE 0 END)
FROM CMPDATA
GROUP BY FORGNUM
;
sum((case when fsal200 >0 then 1
else 0
end )),--count(FempNum),
sum(FSAL200) from cmpdata group by forgnum
在里count面加case when 判断就可以了, count 默认情况下对null值不做计算
(
ForgNum NUMBER(2),
FempNum NUMBER(2),
FSAL200 NUMBER(4)
);INSERT INTO T148 VALUES(1, 1, -10);
INSERT INTO T148 VALUES(1, 2, 20);
INSERT INTO T148 VALUES(1, 3, 30);
INSERT INTO T148 VALUES(1, 4, 40);INSERT INTO T148 VALUES(2, 1, -10);
INSERT INTO T148 VALUES(2, 2, -20);
INSERT INTO T148 VALUES(2, 3, 30);
INSERT INTO T148 VALUES(2, 4, 40);INSERT INTO T148 VALUES(3, 1, -10);
INSERT INTO T148 VALUES(3, 2, -20);
INSERT INTO T148 VALUES(3, 3, -30);
INSERT INTO T148 VALUES(3, 4, 40);
测试结果: