how to apply filter to each row after the group by in sql

Question

i have a table - Table=emp

eno ename designation sal   mgr  deptno
1   aaa   salesman    7000  2    10
2   bbb   manager     17000 3    10
3   ccc   president   40000 null 30
4   ddd   clerk       5000  5    20
5   eee   manager     20000 3    20

Q)per job how many employees earn more than 10000 ?

i want result -

designation  count(*)
salesman     0
manager      2
clerk        0
president    1

query-

SELECT designation, count(*) FROM emp WHERE sal>10000 GROUP BY designation;

getting result-

designation count(*)
manager     2
president   1

Please tell me how to apply filter on each row after group by clause ?

Answer 1

SELECT designation, sum(case when sal > 10000 then 1 else 0 end) as count
FROM emp 
GROUP BY designation;

Answer 2

Use SUM AND GROUP BY functions :

  SELECT designation, SUM(CASE WHEN sal > 10000 THEN 1 ELSE 0 END) AS count
  FROM Your_table 
  GROUP BY designation;