CODEWITHSUNDEEP
sqloracle-databaseselect
depecheSoul
depecheSoul

Reputation: 956

Getting the percentage in sql query

I need to find the percentage of the assistants that are working for some specified professors from the total number of the professors.

So far I've made an SQL order that list all of the assistants that are working for the specified professors, but when I try do divide the number it gives me error.

This is what i wrote:

select count(*) 
from professoren p, assistenten a 
where a.boss = p.persnr and p.name in ('Sokrates', 'Russel', 'Augustinus');

I tried something like this, but it gives me error:

select count(*)/(select count(*) from assistenten)
from professoren p, assistenten a 
where a.boss = p.persnr and p.name in ('Sokrates', 'Russel', 'Augustinus');

Upvotes: 2

Views: 75

Answers (2)

radar
radar

Reputation: 13425

Use explicit join syntax and one approach is to use cross join and get the total Count and then that value can be used to get the percentage.

select count(*)/T.TotalCount
from professoren p
JOIN assistenten a 
where a.boss = p.persnr 
and p.name in ('Sokrates', 'Russel', 'Augustinus')
cross join ( select count(*) as TotalCount from assistenten ) T

Upvotes: 2

Mureinik
Mureinik

Reputation: 312219

One option is to apply a case expression in the to check which professor the assistant reports to: 

SELECT COUNT(CASE WHEN p.name IN ('Sokrates', 'Russel', 'Augustinus') THEN 1 
                  ELSE NULL 
             END) / 
       COUNT(*)
FROM   professoren p, assistenten a 
WHERE  a.boss = p.persnr

EDIT:
Implicit joins have been deprecated for a while now. It's probably better to use an explicit join:

SELECT COUNT(CASE WHEN p.name IN ('Sokrates', 'Russel', 'Augustinus') THEN 1 
                  ELSE NULL 
             END) / 
       COUNT(*)
FROM   assistenten a 
JOIN   professoren p ON a.boss = p.persnr

Upvotes: 2

Related Questions