CODEWITHSUNDEEP
sqloracle-databasepercentage
enriq
enriq

Reputation: 83

SQL: Find percentage of tasks completed based on the status

I have a table that lists the tasks along with a label to indicate if they are completed or not. The table looks like this:

Task | Status

Task1 | Done

Task2 | Done

Task3 | Not done

Task4 | Done

Task5 | Not done

I'm trying to come up with a query that will list the percentage of completed tasks and the output should look like this:

Total Tasks | Completed | Completed %

        5     |       3       |      60%

The problem I'm facing is I'm not able to figure out how to count the completed and not completed tasks to find the percentage.

Upvotes: 0

Views: 1116

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do conditional aggregation `completed

select totaltask, completed,  100 * completed / totaltask as CompletedPercentage 
from (select count(*) as totaltask, 
             sum(case when status = 'Done' then 1 else 0 end) as completed
      from table
      ) t;

Upvotes: 0

Ullas
Ullas

Reputation: 11556

Use COUNT function to find the total count. Then find the count of completed using a combination of SUM function and CASE expression. And using these 2 values, find the percentage of completed.

Query

select t.TotalTask, t.Completed, 
(cast(t.Completed as float) / cast(t.TotalTask as float)) * 100 as CompletedPercentage 
from (
        select count(task) as TotalTask
        , sum(case status when 'Done' then 1 else 0 end) as Completed
        from your_table_name
) as t;

Find a demo here

Upvotes: 1

Related Questions