How sum with case conditional statement works in sql

Question

The other day, I gave an answer to this question but then other user solved that problem with sum + case conditional statement to add one edge condition in result. So, the question came to my mind, how statement sum(case when jobname = 'Analyst' then 1 else 0 end) in the below query works

select d.*
from (select deptno,
         sum(case when jobname = 'Analyst' then 1 else 0 end) as numAnalysts
      from employees
      group by deptno
      order by numAnalysts asc
     ) d
where rownum = 1;`

and return the number of employees over a department. Also, I would like to understand the performance of this query.

Before posting this question, I read this, this and this but still didn't get how this works.

Answer 1

Presumably, this is the part that you are struggling to understand:

  select deptno,
         sum(case when jobname = 'Analyst' then 1 else 0 end) as numAnalysts
  from employees
  group by deptno

This is a simple aggregation query, really. What the query is doing is:

• Look at each row in employees
• If jobname is 'Analyst' then assign the value of 1 (this is the case statement. Otherwise, assign a value of0`.
• Aggregate by department, summing the value just calculated. This has the effect of counting the number of analysts.

case is an expression that returns a value. The sum() is simply adding up that value for each group.