SQL how to select countries that appear every year?

Question

I have a datatable with a column "year" and a column "country". Each country can appear more than one year, ranging from 1900 to 2021. How to select only countries that appear in all the year (1900 to 2021)?

This is my code in selecting which is the earliest and latest year:

SELECT
    MIN(year) AS EarliestYear,  
    MAX(year) AS LatestYear
FROM 
    owid_energy_data;

Please include table columns and sample data.

Pavan Kumar V
– Pavan Kumar V

2022-11-01 04:07:32 +00:00
Commented Nov 1, 2022 at 4:07 — Pavan Kumar V
– Pavan Kumar V, Commented Nov 1, 2022 at 4:07
What DBMS you are using?

ahmed
– ahmed

2022-11-01 04:19:00 +00:00
Commented Nov 1, 2022 at 4:19 — ahmed
– ahmed, Commented Nov 1, 2022 at 4:19

BeRT2me · Accepted Answer · 2022-11-01 18:22:56Z

2

Generalizing off of Extreme_Tough's answer:

SELECT sub.country
  FROM owid_energy_data
 GROUP BY country
HAVING COUNT(DISTINCT year) = (SELECT MAX(year) - MIN(year) + 1
                                 FROM owid_energy_data) range

The purpose of the DISTINCT is to prevent selecting countries where for some years they appear multiple times, but are missing for other years.

edited Nov 1, 2022 at 18:22

answered Nov 1, 2022 at 4:48

BeRT2me

13.3k2 gold badges18 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Extreme_Tough · Accepted Answer · 2022-11-01 04:23:04Z

1

Group by country and take the count of rows. Find out if the count of rows match the number of years in the range (2021 - 1900 + 1)

SELECT country, count(year) from owid_energy_data
GROUP BY country HAVING count(year)= 2021 - 1900 + 1 ;

answered Nov 1, 2022 at 4:23

Extreme_Tough

11413 bronze badges

1 Comment

BeRT2me Over a year ago

You don't need the count(year) in your SELECT if it's not going to provide anything new/useful~

Collectives™ on Stack Overflow

SQL how to select countries that appear every year?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related