SQL Mathematical operator between queries

I’m wondering what the best method would be of using two separate query results in a mathematical operation.

select count(*) as french_films
from language l 
join film f on l.language_id = f.original_language_id
where l.name = 'French'
group by l.name

select count(*) as non_english_films
from language l 
join film f on l.language_id = f.original_language_id
where l.name != 'English'

The code itself produces a result of 12 for the first query and 59 for the second query.

I want to express the 1st number (12) as a percentage of the 2nd (59) which should be around 20.34%.

I’ve attempted this as 2 separate CTEs however am struggling on what is best to join them on or if that is the best method.

Thank you in advance for your help.

Answer

Use conditional aggregation:

select sum(case when l.name = 'French' then 1 else 0 end) as french_films,
       sum(case when l.name <> 'English' then 1 else 0 end) as non_english_films
from language l join
     film f
     on l.language_id = f.original_language_id;

Advertisement

Answer