How to get for a percentage here in below query?

Question

A small change: report for each year the percentage of movies in that year with only female actors, and the total number of movies made that year.

SELECT Year,
       COUNT(*),
       (COUNT(*)*100/(count(Movie.mid)*1.0))
FROM Movie
WHERE TRIM(MID) IN (SELECT TRIM(MID)
                    FROM M_Cast
                    WHERE TRIM(PID) IN (SELECT TRIM(PID)
                                        FROM PERSON
                                        WHERE Gender ='Female'
                                       )
                   )
GROUP BY Movie.Year
ORDER BY COUNT(*) DESC

What should I change in this to get the correct percentage?

Answer 1

I'll approach it with a new DB design that suits the purpose.

So the Movie entity has a n:m relation with the Person entity. A person is cast in many movies and a movie has many persons in its cast. The movie entity has an year column and the person entity has a gender column.

So create this database as done at SQLFiddle here. Create two tables by queries as follows.

DROP TABLE IF EXISTS temp1;
DROP TABLE IF EXISTS temp2;
CREATE TABLE temp1 (
  SELECT COUNT(Pname) AS pcount, 
    Pname, 
    YEAR 
  FROM Movie m, 
    Person p, 
    M_Cast MC 
  WHERE  gender="Female" 
  AND m.mid=mc.mid 
  AND p.pid=mc.pid  
  GROUP BY YEAR,pname
);

create table temp2 
select Year, sum(pcount) as sumyear 
from temp1 
group by Year;

Then query the two temp tables as so:

select
  t1.pname,
  t1.year,
  t1.pcount as MoviesByPerson,
  t2.sumyear MoviesInYear, 
  t1.pcount*100/t2.sumyear as percentage 
from temp1 t1, temp2 t2 
where t1.year=t2.year 

You can run the SQL Fiddle snippet and see the result.