Accumulate values into an array with Postgres

Question

Currently I have this query:

select 
 sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price,
 sum(case when my_table.property_type_mapped = 'SEMIDETACHED' then my_table.price else 0 end) as Semidetached_Current_Asking_Price
from 
 my_table;

So if my_table has the values:

property_type | price
--------------+-------
FLAT | 5000
SEMIDETACHED | 9000
FLAT | 6000

the query will return:

Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
11000 | 9000

How can I replace the sum to accumulate the values into arrays to get?

Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
{5000, 6000} | {9000}

Answer 1

If your PostggreSQL version is 9.4 or later use FILTER clause:

select
 array_agg(my_table.price) filter(where my_table.property_type = 'FLAT' ) as Flat_Current_Asking_Price,
 array_agg(my_table.price) filter(where my_table.property_type = 'SEMIDETACHED') as Semidetached_Current_Asking_Price
from 
 my_table;

Answer 2

If you want to have the arrays, use array_agg, instead of SUM:

SELECT
 array_agg(case when my_table.property_type = 'FLAT' then my_table.price end) as Flat_Current_Asking_Price,
 array_agg(case when my_table.property_type = 'SEMIDETACHED' then my_table.price end) as Semidetached_Current_Asking_Price
FROM 
 my_table;

flat_current_asking_price | semidetached_current_asking_price
:------------------------ | :--------------------------------
{5000,NULL,6000} | {NULL,9000,NULL} 

If you want to get rid of the nulls, use instead a smallish trick (array_to_string gets rid of them):

SELECT
 string_to_array(array_to_string(array_agg(case when my_table.property_type = 'FLAT' then my_table.price end), '|'), '|') as Flat_Current_Asking_Price,
 string_to_array(array_to_string(array_agg(case when my_table.property_type = 'SEMIDETACHED' then my_table.price end), '|'), '|') as Semidetached_Current_Asking_Price
FROM 
 my_table;

flat_current_asking_price | semidetached_current_asking_price
:------------------------ | :--------------------------------
{5000,6000} | {9000} 

dbfiddle here

---

addendum

Per commment of Abelisto, you can also use array_remove:

SELECT
 array_remove(array_agg(case when my_table.property_type = 'FLAT' then my_table.price end), NULL) as Flat_Current_Asking_Price,
 array_remove(array_agg(case when my_table.property_type = 'SEMIDETACHED' then my_table.price end), NULL) as Semidetached_Current_Asking_Price
FROM 
 my_table;

dbfiddle here

• Abelisto: BTW: array_remove() function is able to remove NULLs: select array_remove(array[1,null,3], null);