CODEWITHSUNDEEP
sql
Manali
Manali

Reputation: 53

SQL : Percentage Completed

I need to have a SQL query to calculate the percentage of courses completed by location which are different SQL tables.

Courses table has a Status = 'C' (Completed status). 

select Locations.Name, ( ??? ) as PercentCompleted
from Locations inner join Candidates ON Locations.Id = Candidates.SpecifiedLocation
inner join Courses on Candidates.Id = Courses.CandidateId
Group By Locations.Name

I want the results to be:

Location   PercentCompleted
Loc1         10
Loc2         50
Loc3         75

where 10, 50 and 75 are percentages of courses completed per location.

Can this be achieved with a single SQL query?

Upvotes: 2

Views: 578

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

try like below

select Locations.Name, (sum(case when Status = 'C'  then 1 else 0 end)/(select count(*)
from Candidates c where c.SpecifiedLocation=Locations.Id))*100
  as PercentCompleted
from Locations inner join Candidates ON Locations.Id = Candidates.SpecifiedLocation
inner join Courses on Candidates.Id = Courses.CandidateId
Group By Locations.Name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If I understand correctly, I think you can do:

select l.Name,
       avg(case when co.status = 'C' then 100.0 else 0 end) as PercentCompleted
from Locations l inner join
     Candidates c
     on l.Id = c.SpecifiedLocation inner join
     Courses co
     on c.Id = co.CandidateId
group by l.name;

Upvotes: 1

Related Questions