alwaysaskingquestions alwaysaskingquestions - 22 days ago 9
SQL Question

How to keep a bucket using case statement even if the count for items in that bucket is 0?

Here's the data table named "Salary_table" that i've created for this question:
enter image description here

So I want to find the number of employees in each salary bucket in each department. the buckets are

"<$100" "$100-$200" and ">$200"


The desired output is:

enter image description here

Below is my code for achieving this task:

select distinct(st.department) as "Department",
sb.salary_bucket as "salary range", count(*)
from Salary_table st
Left join (
select department, employee, case
when salary < 100 then "<$100"
when salary between 100 and 200 then "$100-$200"
else ">$200"
end
as salary_bucket
from Salary_table
) sb
on sb.employee = st.employee
group by st.department, sb.salary_bucket
order by st.department, sb.salary_bucket
;


but my output is a bit short of what im expecting:

enter image description here

There are TWO problems with my current output:


  1. The buckets with 0 employees earning the salary in the bucket range are not listed; I want it to be listed with a value "0"

  2. The salary bucket is NOT in the right order, even though I added in the statement "order by" but I think it's b/c its texts so can't really do that.



I would really appreciate some hints and pointers on how to fix/achieve these two issues I've addressed above. Thank you so much!

what i've tried


  1. I tried use "left join" but output came out the same

  2. I tried adding the "order by" clause but doesnt seem to work on text buckets


Answer

You are sort of on the right track, but the idea is a bit more complicated. Use a cross join to get all the rows -- the buckets and departments. Then use left join to bring in the matching information and finally group by for the aggregation:

select d.department, b.salary_bucket,
       count(sb.department) as cnt
from (select '<$100' as salary_bucket union all
      select '$100-$200' union all
      select '>$200'
     ) b cross join
     (select distinct department from salary_table
     ) d left join
     (select department, employee,
             (case when salary < 100 then '<$100'
                   when salary between 100 and 200 then '$100-$200'
                   else '>$200'
              end) as salary_bucket
      from Salary_table
     ) sb
     on sb.department = d.department and
        sb.salary_bucket = b.salary_bucket
group by d.department, b.salary_bucket;