One SQL statement for multiple column totals based on year

Question

I'm trying to return the total number of unique countries listed for each year, and the total number of unique countries in the entire table. The table is formatted like this:

Country | Year | State | V1 | V2 |
 US       2020      NY      9    2
 US       2020      MA      3    6
 CA       2020      MAN     2    8
 CA       2020      ONT     5    1
 AU       2020      TAS     7    2
 AU       2020      VIC     3    3
 US       2021      NY      2    0
 US       2021      MA      8    2
 AU       2021      TAS     4    1
 AU       2021      VIC     5    2

I want my query to return this:

2020  |  2021 | Total_Unique_Countries
3         2               3

I tried:

SELECT
   SUM(CASE WHEN YEAR=2020 THEN 1 ELSE 0 END) AS "2020",
   SUM(CASE WHEN YEAR=2021 THEN 1 ELSE 0 END) AS "2021",
   COUNT(DISTINCT COUNTRY) AS Total_Unique_Countries
FROM MYTABLE GROUP BY YEAR

The result:

2020 | 2021 | Total_Unique_Countries
6       0               3
0       4               2

Answer 1

SELECT
   COUNT(DISTINCT CASE WHEN YEAR=2020 THEN COUNTRY END) AS "2020",
   COUNT(DISTINCT CASE WHEN YEAR=2021 THEN COUNTRY END) AS "2021",
   COUNT(DISTINCT COUNTRY) AS Total_Unique_Countries
FROM MYTABLE

This should give you the result you are looking for.