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

@jarlh That returns the total number of rows for each year. I'm trying to get the total number of countries for each year — toadvenom23, Feb 15 '23 at 19:00
Maybe try this: 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 GROUP BY YEAR — Razor, Feb 15 '23 at 19:03
@Razor That got me closer to what I need, but if you look at my edited code above, you can see the problem I'm still having with multiple rows and the Total column being incorrect — toadvenom23, Feb 15 '23 at 19:16
Accidently deleted above comment. Just remove "group by" from Razor's answer above. It's the same suggestion as jarlh in comment1. — Isolated, Feb 15 '23 at 19:20

score 0 · Answer 1 · answered Feb 15 '23 at 19:15

You can first elimnate the duplicates in a CTE and then count

WITH CTE as (SELECT
   DISTINCT "Country", "Year" FROM MYTABLE)
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 CTE

2020	2021	total_unique_countries
3	2	3

SELECT 1

fiddle

score 0 · Accepted Answer · answered Feb 15 '23 at 19:28

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.

One SQL statement for multiple column totals based on year

2 Answers2