From: | Owen Hartnett <owen(at)clipboardinc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SQL question on chunking aggregates |
Date: | 2014-03-04 19:49:22 |
Message-ID: | FF271F71-28B7-4983-B429-D58331B9E90C@clipboardinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2014-03-04 20:05:49 | Re: SQL question on chunking aggregates |
Previous Message | Adrian Klaver | 2014-03-04 16:00:13 | Re: Role Inheritance Without Explicit Naming? |