Skip site navigation (1) Skip section navigation (2)

Aggregate from CASE WHEN...

From: Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Aggregate from CASE WHEN...
Date: 2012-07-08 01:46:48
Message-ID: 4FF8E688.7060301@anatec.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello,

I am trying to aggregate some data but conditionally as follows:

SELECT
         feed_all_y2012m01.array_accum(message_copies.msg_id) as 
messages_array,
         uniq(feed_all_y2012m01.array_accum(obj_mmsi)) as mmsi_array,
         CASE WHEN msg_type BETWEEN  1 and  3 THEN 
feed_all_y2012m01.array_accum(message_copies.msg_id) END as msgA,
         CASE WHEN msg_type = 18 THEN 
feed_all_y2012m01.array_accum(message_copies.msg_id) END as msgB_std,
         CASE WHEN msg_type = 19 THEN 
feed_all_y2012m01.array_accum(message_copies.msg_id) END as msgB_ext,
         CASE WHEN obj_type = 'SHIP_TYPE_A' THEN 
uniq(feed_all_y2012m01.array_accum(obj_mmsi)) END as mmsi_type_a_array,
         CASE WHEN obj_type = 'SHIP_TYPE_B' THEN 
uniq(feed_all_y2012m01.array_accum(obj_mmsi)) END as mmsi_type_b_array,
         .......

The logic breaks where PGSQL forces me(?) to group by msg_type and 
obj_type while I was hoping that msg_type and obj_type could just be 
considered as the variables that the conditions would be checked against.
Neither I want to use some form of aggregation on these two fields. Is 
there any way that I can achieve something like this?

Thank you
Yiannis

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2012-07-08 15:24:46
Subject: Re: Aggregate from CASE WHEN...
Previous:From: Gurjeet SinghDate: 2012-07-07 04:01:49
Subject: Re: Recheck condition...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group