Search Postgresql Archives

SQL question on chunking aggregates

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi all:

I have a table that has multiple records for a single owner_id.  I'm able to use array_arg to combine the records into a single row, which works fine.  I'm using this sql:

select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue + revalbuildingvalues) from parcel group by owner_id;

Which results in the following (sometimes there's only one record per aggregate, sometimes multiple):

1030600;"{"154    191"}";"{244690}"
1030900;"{"22    202"}";"{217210}"
1031130;"{"113    135","113    138","113    132","113    130","113    133","113    127","113    126","113    131","113    129","113    136","113    125","113    137","113    134","113    128"}";"{7700,7700,7700,7700,7700,7700,7700,7700,7700,191770,7700,7700,7700,7700}"

What I want to do, is where there are more than 5 rows involved in the aggregate, as in the last example, to split it into multiple rows of 5 aggregated rows.  It's for a mailing list and I want to combine like addresses into one record, but if I'm over 5, I have to print the rest on a separate letter.

1031130;"{"113    135","113    138","113    132","113    130","113    133"}";"{7700,7700,7700,7700,7700}"
1031130;"{"113    127","113    126","113    131","113    129","113    136"}";"{7700, 7700,7700,7700,191770}"
1031130;"{"113    125","113    137","113    134","113    128"}";"{7700,7700,7700,7700}"

 It looks like I should be able to use the window function to do this, but I've been unsuccessful.  The following runs, but doesn't seem to have any effect:

select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue + revalbuildingvalues) from parcel group by owner_id
window mywindow as (rows between current row and 5 following);

Does anyone have any suggestions on what I should try?

-Owen

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux