SQL - Difference between two queries

Question

I have two SQL queries that get the average temperature of a countries states and for the country overall, for each year:

SELECT strftime('%Y', date) as valYear, AVG(averageTemperature) 
FROM state 
WHERE country = 'Africa' 
GROUP BY valYear

SELECT strftime('%Y', date) as valYear, AVG(averageTemperature) 
FROM country 
WHERE country = 'Africa' 
GROUP BY valYear

What I want to do is calculate the differences between the state and national for each year. So for example:

National data

Year  AverageTemp
-----------------
1954  17.5
1955  18

and State Data:

Year  AverageTemp
-----------------
1954  15.5
1955  15

Would yield:

Difference

     Year  Diff
     -----------
     1954  2
     1955  3

I've tried doing something like this but it doesn't produce the right answer.

SELECT
    s.date, (c.averageTemperature - s.averageTemperature) AS Difference 
FROM 
    state s 
INNER JOIN 
    country c ON s.date = c.date 
WHERE
    s.country = 'Africa' AND c.country = 'Africa'

SQL - Difference between two queries

2 Answers