Сумма с группой в postgres

Из вашего результата вы пропускаете Испанию, потому что у нее нет группы.

Итак, вы можете сделать это так

WITH SUMCTE AS (SELECT "country_name", MAX("group_name") as group_name, SUM("percent_value") AS percent_value
FROM tab1
GROUP BY "country_name"),
CTE_ROW_NUM AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY "group_name" ORDER BY "percent_value" DESC ) rn 
FROM SUMCTE
WHERE "group_name" IS NOT NULL)
SELECT "country_name", "group_name", "percent_value"
FROM CTE_ROW_NUM
WHERE rn = 1
ORDER BY  "percent_value" DESC

country_name | group_name | percent_value
:----------- | :--------- | ------------:
US           | EMEA       |         35.00
AP           | Domestic   |         20.00

дб <> рабочий пример здесь

ПРИМЕРНЫЕ ДАННЫЕ РЕЗУЛЬТАТЫ

    WITH country_level_info AS (
        SELECT
          country_name,
          MAX(group_name) AS group_name, -- MAXIMUM ON GROUP NAME WILL CHOOSE THE NON NULL VALUE WHICH IS WHAT WE WANT
          SUM(percent_value) AS sum_percent
        FROM data 
        GROUP BY 1 
        HAVING MAX(group_name) != '' OR  MAX(group_name) != NULL
        ),
        ranking AS (
        SELECT  
          *,
          ROW_NUMBER() OVER(PARTITION BY group_name ORDER BY sum_percent DESC) AS rank_
        FROM country_level_info
        )
        SELECT country_name, 
               group_name, 
               sum_percent 
        FROM ranking WHERE rank_ = 1
        ORDER BY sum_percent DESC