Select all values from last date that is shared between rows grouped by a value

user_11864173 0
Viewed 68

I have a Postgresql table with a list of values for countries over time, and their continents. Values can be NULL. I’d like to get the sum for each continent over time, up to the latest date each continent has data for.

This is my table (view on DB Fiddle):

| continent | country | date       | value | id  |
| --------- | ------- | ---------- | ----- | --- |
| Europe    | Germany | 2020-05-25 | 10    | 1   |
| Europe    | Germany | 2020-05-26 | 11    | 2   |
| Europe    | Germany | 2020-05-27 | 12    | 3   |
| Europe    | Germany | 2020-05-28 | 13    | 4   |
| Europe    | Italy   | 2020-05-25 | 20    | 5   |
| Europe    | Italy   | 2020-05-26 | 21    | 6   |
| Europe    | Italy   | 2020-05-27 | 22    | 7   |
| Europe    | Italy   | 2020-05-28 | 23    | 8   |
| Europe    | France  | 2020-05-25 | 30    | 9   |
| Europe    | France  | 2020-05-26 | 31    | 10  |
| Europe    | France  | 2020-05-27 | 32    | 11  |
| Europe    | France  | 2020-05-28 | NULL  | 12  |
| Africa    | Congo   | 2020-05-25 | 40    | 13  |
| Africa    | Congo   | 2020-05-26 | 41    | 14  |
| Africa    | Congo   | 2020-05-27 | NULL  | 15  |

And this is what I’d like to get back. Note that the Europe includes data up to the 27th, because France has no data for the 28th, and Africa up to the 26th, because that’s the last date its countries have data for.

| continent | date       | value |
| --------- | ---------- | ----- |
| Europe    | 2020-05-27 | 66    |
| Africa    | 2020-05-26 | 41    |
| Europe    | 2020-05-26 | 63    |
| Africa    | 2020-05-25 | 40    |
| Europe    | 2020-05-25 | 60    |

I managed to almost get there by including the number of countries per continent that have data on each date.

SELECT
    countries.continent,
    countries.date,
    SUM(countries.value) AS value,
    COUNT(countries.country) AS countries_count
FROM
    countries
WHERE
    countries.value IS NOT NULL
GROUP BY
    countries.continent,
    countries.date
ORDER BY
    countries.date DESC,
    countries.continent;

| continent | date       | value | countries_count |
| --------- | ---------- | ----- | --------------- |
| Europe    | 2020-05-28 | 36    | 2               |
| Europe    | 2020-05-27 | 66    | 3               |
| Africa    | 2020-05-26 | 41    | 1               |
| Europe    | 2020-05-26 | 63    | 3               |
| Africa    | 2020-05-25 | 40    | 1               |
| Europe    | 2020-05-25 | 60    | 3               |

I also managed to get the number of countries per continent.

SELECT
    countries.continent,
    COUNT(DISTINCT countries.country) as number_of_countries
FROM
    countries
GROUP BY
    countries.continent;

| continent | number_of_countries |
| --------- | ------------------- |
| Africa    | 1                   |
| Europe    | 3                   |

I’m stuck on how to combine the two queries to filter out rows that haven’t got the full number of countries for the continent (e. g. select rows where countries_count is 3 for Europe and 1 for Africa.

This is the end result I’d like to get back:

| continent | date       | value |
| --------- | ---------- | ----- |
| Europe    | 2020-05-27 | 66    |
| Africa    | 2020-05-26 | 41    |
| Europe    | 2020-05-26 | 63    |
| Africa    | 2020-05-25 | 40    |
| Europe    | 2020-05-25 | 60    |

Or maybe there’s a completely different way to go about this?

View on DB Fiddle

postgresql sql
4 Answers

You can use NOT IN within your WHERE Clause :

SELECT
    c.continent,
    c.date,
    SUM(c.value) AS value,
    COUNT(DISTINCT c.country) AS countries_count
FROM countries c
WHERE date NOT IN 
    ( SELECT date 
        FROM countries 
       WHERE value IS NULL )
GROUP BY c.continent, c.date
ORDER BY c.date DESC, c.continent;
user_58413060

You can compare the number of countries on the continent to the number available on each date -- and then just use dates where the two match ("complete data").

Unfortunately, Postgres does not support count(distinct) as a window function. But you can do:

SELECT c.continent, c.date,
       SUM(c.value) AS value,
        COUNT(c.country) AS countries_count
FROM (SELECT c.*,
             COUNT(*) OVER (PARTITION BY continent, date) as num_on_date
      FROM countries c
      WHERE value IS NOT NULL
     ) c JOIN
     (SELECT continent, COUNT(DISTINCT country) as num_countries
      FROM countries
      GROUP BY continent
     ) cc
     ON cc.continent = c.continent
WHERE num_on_date = num_countries
GROUP BY c.continent, c.date
ORDER BY c.date DESC, c.continent;

Here is a db<>fiddle.

You can also do this with a filter in the HAVING clause:

SELECT c.continent, c.date,
       SUM(c.value) AS value,
        COUNT(c.country) AS countries_count
FROM countries c
WHERE value IS NOT NULL
GROUP BY c.continent, c.date
HAVING COUNT(*) = (SELECT COUNT(DISTINCT c2.country)
                   FROM countries c2
                   WHERE c2.continent = c.continent
                  )
ORDER BY c.date DESC, c.continent;

This does the aggregation and then only keeps the rows where the number of rows matches the number of countries.

user_11440350

You can filter with a having clause to exclude groups where any country is null

SELECT
    continent,
    date,
    SUM(value) AS value
FROM countries
GROUP BY continent, date
HAVING BOOL_AND(value is not null)
ORDER BY date DESC, continent
user_106767160

With SUM() window function:

select distinct c.continent, c.date, 
  sum(c.value) over (partition by c.continent, c.date) "value"
from countries c
where not exists (
  select 1 from countries
  where continent = c.continent and date = c.date and value is null
)  
order by c.date desc, c.continent;

See the demo.
Results:

| continent | date                     | value |
| --------- | ------------------------ | ----- |
| Europe    | 2020-05-27T00:00:00.000Z | 66    |
| Africa    | 2020-05-26T00:00:00.000Z | 41    |
| Europe    | 2020-05-26T00:00:00.000Z | 63    |
| Africa    | 2020-05-25T00:00:00.000Z | 40    |
| Europe    | 2020-05-25T00:00:00.000Z | 60    |
user_104988280
Related