write a query to find the percentage of employee using company table whose status is 1, -1, -2 [clos-CodePudding

I'm trying to figure out a query which show the percent of employees whose status is 1,-1,-2 .

Here the table name and fields:

table: company(company_code,employee_id,status)

CodePudding user response：

Maybe, this query will help you out

SELECT status, 
           count(employee_id) as e_count,
           count(employee_id) * 100.0 / (select count(*) from company) as e_percent
    FROM company
    group by status

This query here gives you all the percentages for each and every status code you've got in your table. in case if you're looking for specific status code put that status code in where clause for more accuracy.

CodePudding user response：

Here's an approach for solving it (without grouping by status). In this sample, I declared the status field only, as the other fields (company_code and employee_id) do not play a role here:

declare @company table (status int not null);
insert into @company values (1), (1), (1), (-1), (-2), (-2);

select 
  count(*) as cnt_all, 
  count(IIF(status = 1, status, null)) as cnt_status_1,
  count(IIF(status = -1, status, null)) as cnt_status_m1,
  count(IIF(status = -2, status, null)) as cnt_status_m2,
  count(IIF(status = 1, status, null)) * 100.0 / nullif(count(*), 0) as percentage_status_1,
  count(IIF(status = -1, status, null)) * 100.0 / nullif(count(*), 0) as percentage_status_m1,
  count(IIF(status = -2, status, null)) * 100.0 / nullif(count(*), 0) as percentage_status_m2
from @company;

This will give you these results:

cnt_all: 6
cnt_status_1: 3
cnt_status_m1: 1
cnt_status_m2: 2
percentage_status_1: 50.000000000000
percentage_status_m1: 16.666666666666
percentage_status_m2: 33.333333333333

Please note that multiplying with 100.0 (and not just with the integer value 100) is important to turn the count value into a floating point. And the nullif(count(*), 0) expression is used to avoid a "divide by zero" error in case your table is actually empty (the resulting value will be NULL in that case).