Double aggregation in postgresql

Question

I have a postgresql table like this (but with 14 mln records)

organization    inn income  parameter
org1            111 10      apple
org2            222 4       pineapple
org1            111 6       orange
org1            111 7       pineapple
org1            111 3       orange
org2            222 8       apple
org2            222 1       orange
org1            111 9       pineapple

I need to calculate sum income for each organization, but also find the parameter with max sum income inside each organization

The result I want is following:

organization inn total_income max_parameter max_parameter_income
org 1        111 35           pineapple     16
org 2        222 13           apple         8

(actually, I have four parameter columns and I have to do similar thing with every parameter for each organization)

How could I do this?

which version of postgresql?

Chris Travers
– Chris Travers

2016-08-11 11:08:39 +00:00
Commented Aug 11, 2016 at 11:08 — Chris Travers
– Chris Travers, Commented Aug 11, 2016 at 11:08

Gordon Linoff · Accepted Answer · 2016-08-11 11:15:43Z

I would approach this as:

select organization, sum(total_income) as total_income,
       max(case when seqnum = 1 then parameter end) as max_parameter,
       sum(case when seqnum = 1 then total_income end) as max_parameter_income
from (select organization, inn, parameter, sum(income) as total_income,
             row_number() over (partition by organization, inn order by sum(income) desc) as seqnum
      from likethis
      group by organization, inn, parameter
     ) t
group organization, inn;

In Postgres, you can actually do this without explicit aggregation:

select distinct on (organization, inn) organization, inn,
       total_income, parameter as max_parameter,
       max_parameter_income
from (select organization, inn, parameter,
             sum(income) over (partition by organization, inn) as total_income,
             sum(income) over (partition by organization, inn, parameter) as parameter_income
      from likethis
     ) t
order by organization, inn, parameter_income desc;

I would use the first method. Sometimes it is fun to use useful Postgres extensions.

Collectives™ on Stack Overflow

Double aggregation in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related