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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-07-08 15:24:46 | Re: Aggregate from CASE WHEN... |
Previous Message | Gurjeet Singh | 2012-07-07 04:01:49 | Re: Recheck condition... |