SQL percent query

I have two tables that I wont to query some data from. The problem is that the query never stops and never gives any result.

The task is to get the percent of all male actors.

filmparticipation(partid, personid, filmid, parttype)

person(personid, lastname, firstname, gender)

Her is my try, can someone please give me a hint to achive the task?

SELECT (COUNT(p.personid) / COUNT(a.p开发者_C百科erson)) * 100
FROM person p, person a, filmparticipation f
WHERE
f.parttype = 'cast' AND
p.gender = 'M';

---

You had no ON clauses for your joins, so you were joining every record with every other record across three tables! Instead, try something like this:

select (count(case when p.gender = 'M' then 1 end) / count(*)) * 100
from person p
inner join filmparticipation f on p.personid = f.personid
where f.parttype = 'cast' 

---

How about:

SELECT (COUNT(p.personid) / subq.total) * 100
FROM person p, (select count(personID) as total from person) subq, filmparticipation f
WHERE
f.parttype = 'cast' 
and f.personid = p.personid    
and p.gender = 'M';

I think the problem you had before was you selected from person twice and did not join the second selection (a) with anything, possibly leading to a cartesian join (which would return eventually, but maybe not for while).

---

Well, part of the problem is you're not joining the tables - you're not giving the query any relationship between them, and so it's attempting to aggregate all possible combinations of all records in all the tables. You want something like this:

SELECT COUNT(p.personid) AS ActorCount, 
       SUM(CASE WHEN p.gender = 'M' THEN 1 ELSE 0 END) AS MaleActorCount, 
       (SUM(CASE WHEN p.gender = 'M' THEN 1 ELSE 0 END) / COUNT(p.personid) * 100) AS PercentMaleActors
FROM person p
INNER JOIN filmparticipation f ON p.personid = f.personid
WHERE f.parttype = 'cast'