I've a table countries_percent like following:
country_name | group_name | percent_value |
---|---|---|
US | EMEA | 10.00 |
US | 25.00 | |
GB | EMEA | 15.00 |
AP | Domestic | 20.00 |
AK | Domestic | 12.00 |
ES | 5.00 |
Now I would like to select the highest sum of percent_value for each group by country e.g:
country_name | group_name | sum_percent |
---|---|---|
US | EMEA | 35.00 |
AP | Domestic | 20.00 |
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:
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