How to calculate percentages in a group using SQL

Suppose I have a table employee

(ID, status, department)

Suppose the status can only be ‘f’ or ‘c’.

How to calculate the % of f’s and c’s in each department?

Update 1: the four fields in the output should be

A. DEPTNAME B. F_PCT C. C_PCT D. EMPCOUNT in that Dept

Update 2: some departments might have all F’s or all C’s. In either case, they should be reported with either Col B or Col C being 0 and the other being 100.

Answer

You can use a window function to accomplish that:

select department, status,
(count(status) / sum(count(status)) over (partition by department)) * 100.0 as percentage
from employee
group by department, status