select dept, COUNT(salary), count(salary2), count(*) from (
select dept,
case when salary > 200000 then null else salary end salary,
case when lnnvl(salary <= 200000) then null else salary end salary2
from count_sample
)
group by dept;
DEPT COUNT(SALARY) COUNT(SALARY2) COUNT(*)
-------- ------------- -------------- ----------
管理部 0 0 2
開発部 1 1 4
条件別にカウントする その1
select dept, trunc(salary/1000), COUNT(salary)
from count_sample group by dept, trunc(salary/1000)
order by dept, trunc(salary/1000);
DEPT TRUNC(SALARY/1000) COUNT(SALARY)
-------- ------------------ -------------
管理部 300 1
管理部 400 1
開発部 200 1
開発部 500 2
開発部 0
条件別にカウントする その2 (COUNTIF 風)
select dept,
COUNT(case when salary >= 500000 then 'X' else null end) ">=500K",
COUNT(case when salary >= 400000 and salary < 500000 then 'X' else null end) "400K",
COUNT(case when salary >= 300000 and salary < 400000 then 'X' else null end) "300K",
COUNT(case when salary >= 200000 and salary < 300000 then 'X' else null end) "200K",
COUNT(case when salary >= 100000 and salary < 200000 then 'X' else null end) "100K",
COUNT(case when salary < 100000 then 'X' else null end) "<100K",
COUNT(case when salary is null then 'X' else null end) "NULL"
from count_sample group by dept;
DEPT >=500K 400K 300K 200K 100K <100K NULL
------ ------ ------ ------ ------ ------ ------ ------
管理部 0 1 1 0 0 0 0
開発部 2 0 0 1 0 0 1