0

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.

HarryH247
  • 13
  • 4

1 Answers1

1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the quick reply. Small follow up if you can help. It isn't my database and the data type is set to INT as it would always be a whole number of films. My issue is that when i attempt to do: SELECT ((french_films/non_english_films)*100) FROM (AGGREGATION ABOVE) T1 It is returning zero, any suggestions? – HarryH247 Apr 27 '20 at 21:37
  • 1
    SQL Server performs integer division for integer types. See eg https://stackoverflow.com/questions/3443672/integer-division-in-sql-server – David Browne - Microsoft Apr 27 '20 at 21:38
  • 1
    That's perfect! Thank you both for your help :) – HarryH247 Apr 27 '20 at 21:44