Home > Software design >  Sum with group by in postgres
Sum with group by in postgres

Time:05-06

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:

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
  • Related