3

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}

?

4

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;
0

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

  • 1
    BTW: array_remove() function is able to remove NULLs: select array_remove(array[1,null,3], null); – Abelisto Jul 29 '17 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.