CODEWITHSUNDEEP
sqlpostgresql
ThatDataGuy
ThatDataGuy

Reputation: 2109

How to aggregate a column based on another column in postgres

I have a database where I have data for companies coming from different sources. I wish to be able to select data for specific company information (fields) and merge them into a single record per company. I wish to do this without using a 3rd normal form storage so I can still have referential integrity. I also wish to do the merge operation dynamically, and without specific coding for columns.

Example of data and query:

create table test2.company(
identifier int not null
,name varchar(100) null
,marketcap int null --in millions
,field varchar(100) not null
);

insert into test2.company(identifier, name, marketcap,field) values
(1,'Apple',1, 'name')
,(1,'Aplle',1000000,'marketcap')
;

select * from test2.company;

--result

----------------------------------------------
| identifier | name  | marketcap | field     |
| ---------- | ----- | --------- | --------- |
| 1          | Apple | 1         | name      |
| 1          | Aplle | 1000000   | marketcap |
----------------------------------------------

Best I have come up with so far:

with x1 as (select
    c.identifier
    ,case when c.field = 'name' then c.name else null end as name
    ,case when c.field = 'marketcap' then marketcap else null end as marketcap
    from test2.company c
)
, x2 as (select 
    x1.identifier
    ,string_agg(x1.name,'') as name
    ,sum(x1.marketcap) as marketcap
    from x1
    group by x1.identifier
)
select * from x2;

--result

----------------------------------
| identifier | name  | marketcap |
| ---------- | ----- | --------- |
| 1          | Apple | 1000000   |
----------------------------------

As you can see, I've had to specifically code for the columns. Where a data type was a number, I had to use sum, vs string_agg.

Is there some way to do this that is generic?

Upvotes: 1

Views: 1155

Answers (1)

Mahesh H Viraktamath
Mahesh H Viraktamath

Reputation: 884

I guess, this is how genric it can get - 

with x1 as (select
    c.identifier
    ,c.field
    ,string_agg(c.name, '') as name
    ,sum(c.marketcap) as marketcap
    from test2.company c
    group by c.identifier, c.field
)
select x.identifier, 
       (select name from x1 a where a.field = 'name' and a.identfier = x.identifier) as name, 
       (select marketcap from x1 b where b.field = 'marketcap' and b.identfier = x.identifier) as marketcap 
       from x1 x;

Upvotes: 1

Related Questions