I've a table countries_percent like following:

Now I would like to select the highest sum of percent_value for each group by country e.g:

so here US is selected because the sum of its percentage was highest for EMEA group. Please note that NULL group_name is counted towards EMEA because of country_name match. Also note that ES didn't appear as it didn't have any group_name against it and there is no other group with value ES.

Is it possible to query the above with Postgres?

CodePudding user response：

From your Result you bSkip Spain, because it has no group.

So you can do it like this

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

db<>fiddle here

CodePudding user response：

SAMPLE DATA RESULTS

    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