From tbs@navicon.dk Mon Mar 21 21:57:31 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EE51856392 for ; Mon, 21 Mar 2005 21:57:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82836-10 for ; Mon, 21 Mar 2005 21:57:25 +0000 (GMT) Received: from navicon.dk (cpe.atm1-0-1091060.0x50a3778a.arcnxe1.customer.tele.dk [80.163.119.138]) by svr1.postgresql.org (Postfix) with ESMTP id E79E356388 for ; Mon, 21 Mar 2005 21:57:23 +0000 (GMT) Received: from Spartacus (10.0.1.131:3354) by raptus.navicon.dk with [XMail 1.18 (Linux/Ix86) ESMTP Server] id for from ; Mon, 21 Mar 2005 22:57:25 +0100 From: "Thomas Borg Salling" To: Subject: "Flattening" query result into columns Date: Mon, 21 Mar 2005 22:57:13 +0100 Organization: NAVICON A/S MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0074_01C52E69.54F73CA0" X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcUuYPAwb0ESLSlDTxyE4Hl8Qor7FA== Message-Id: <20050321215723.E79E356388@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.849 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_MESSAGE, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200503/288 X-Sequence-Number: 20767 This is a multi-part message in MIME format. ------=_NextPart_000_0074_01C52E69.54F73CA0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I am looking for a way to "flatten" a query result, so that rows are "transposed" into columns, just as asked here for oracle: http://groups.google.dk/groups?hl=da &lr=&client=firefox-a&rls=org.mozilla:en-US:official&selm=aad10be0.040129232 2.7b6c320b%40posting.google.com Is there any way to do this with pgsql ? Thanks, /Thomas. ------=_NextPart_000_0074_01C52E69.54F73CA0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I am looking for a way to = ”flatten” a query result, so that rows are ”transposed” into columns, = just as asked here for oracle:

http://groups.google.dk/groups?hl=3Dda&a= mp;lr=3D&client=3Dfirefox-a&rls=3Dorg.mozilla:en-US:official&= selm=3Daad10be0.0401292322.7b6c320b%40posting.google.com

 

Is there any way to do this with pgsql  = ?

 

Thanks,

/Thomas.

 

 

------=_NextPart_000_0074_01C52E69.54F73CA0-- From leif@solumslekt.org Tue Mar 22 00:39:35 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 64D6E5667D for ; Tue, 22 Mar 2005 00:39:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60766-05 for ; Tue, 22 Mar 2005 00:39:20 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id 5AB36565AD for ; Tue, 22 Mar 2005 00:39:15 +0000 (GMT) Received: from mail48-s.fg.online.no (mail48-s.fg.online.no [148.122.161.48]) by svr4.postgresql.org (Postfix) with ESMTP id 2D9845AFAA2 for ; Mon, 21 Mar 2005 22:42:49 +0000 (GMT) Received: from [192.168.0.57] (062016240144.customer.alfanett.no [62.16.240.144]) by mail48.fg.online.no (8.12.11/8.12.11) with ESMTP id j2LMb85T008358 for ; Mon, 21 Mar 2005 23:37:09 +0100 (CET) From: "Leif B. Kristensen" To: pgsql-sql@postgresql.org Subject: Re: "Flattening" query result into columns Date: Mon, 21 Mar 2005 23:37:08 +0100 User-Agent: KMail/1.7.2 References: <20050321215723.E79E356388@svr1.postgresql.org> In-Reply-To: <20050321215723.E79E356388@svr1.postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200503212337.08682.leif@solumslekt.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.139 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/293 X-Sequence-Number: 20772 On Monday 21 March 2005 22:57, Thomas Borg Salling wrote: > I am looking for a way to "flatten" a query result, so that rows are > "transposed" into columns, just as asked here for oracle: > > Is there any way to do this with pgsql =A0? Just to help out the guys, here's a working link: What you're asking for is called a pivot table, and at least in Oracle=20 they use a function called decode(). I need exactly the same thing, but=20 I too am unable to find the optimal way to do it in PostgreSQL. =2D-=20 Leif Biberg Kristensen http://solumslekt.org/ From sdavis2@mail.nih.gov Mon Mar 21 23:43:46 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D1A9956B4A for ; Mon, 21 Mar 2005 23:43:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27238-09 for ; Mon, 21 Mar 2005 23:43:41 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id 4841356B6C for ; Mon, 21 Mar 2005 23:42:50 +0000 (GMT) Received: from viper.net.nih.gov (mailfwd.nih.gov [165.112.130.32]) by svr4.postgresql.org (Postfix) with ESMTP id 61ABA5AF039 for ; Mon, 21 Mar 2005 23:32:56 +0000 (GMT) Received: from viper.net.nih.gov (localhost [127.0.0.1]) by viper.net.nih.gov (8.12.10/8.12.8p1) with ESMTP id j2LNRmhb018827 for ; Mon, 21 Mar 2005 18:27:48 -0500 Received: from WATSON (vpn-165-112-10-41.net.nih.gov [165.112.10.41]) by viper.net.nih.gov (8.12.10/8.12.8p1) with SMTP id j2LNReN3018753; Mon, 21 Mar 2005 18:27:47 -0500 Message-ID: <002d01c52e6d$93e02360$1f6df345@WATSON> From: "Sean Davis" To: "Thomas Borg Salling" , References: <20050321215723.E79E356388@svr1.postgresql.org> Subject: Re: "Flattening" query result into columns Date: Mon, 21 Mar 2005 18:27:41 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002A_01C52E43.AAD80EE0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.141 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200503/289 X-Sequence-Number: 20768 This is a multi-part message in MIME format. ------=_NextPart_000_002A_01C52E43.AAD80EE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thomas, You probably want a crosstab. There is a contributed module in = contrib/crosstab. If you do a search of the postgres mailing lists, = there will be several posts relating to the same issue. Sean ----- Original Message -----=20 From: Thomas Borg Salling=20 To: pgsql-sql@postgresql.org=20 Sent: Monday, March 21, 2005 4:57 PM Subject: [SQL] "Flattening" query result into columns I am looking for a way to "flatten" a query result, so that rows are = "transposed" into columns, just as asked here for oracle: = http://groups.google.dk/groups?hl=3Dda&lr=3D&client=3Dfirefox-a&rls=3Dorg= .mozilla:en-US:official&selm=3Daad10be0.0401292322.7b6c320b%40posting.goo= gle.com =20 Is there any way to do this with pgsql ? =20 Thanks, /Thomas. =20 =20 ------=_NextPart_000_002A_01C52E43.AAD80EE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thomas,
 
You probably want a crosstab.  = There is a=20 contributed module in contrib/crosstab.  If you do a search of the = postgres=20 mailing lists, there will be several posts relating to the same=20 issue.
 
Sean
 
----- Original Message -----
From:=20 Thomas Borg = Salling=20
Sent: Monday, March 21, 2005 = 4:57=20 PM
Subject: [SQL] "Flattening" = query result=20 into columns

I am looking for a way = to=20 =94flatten=94 a query result, so that rows are =94transposed=94 into = columns, just as=20 asked here for oracle:

http://groups.google.dk/groups?hl=3Dda&a= mp;lr=3D&client=3Dfirefox-a&rls=3Dorg.mozilla:en-US:official&= selm=3Daad10be0.0401292322.7b6c320b%40posting.google.com

 

Is there any way to do = this with=20 pgsql  ?

 

Thanks,

/Thomas.

 

 

------=_NextPart_000_002A_01C52E43.AAD80EE0-- From lists@boutiquenumerique.com Tue Mar 22 00:35:25 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7F62E565AD for ; Tue, 22 Mar 2005 00:35:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60562-06 for ; Tue, 22 Mar 2005 00:35:15 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id DB30455720 for ; Tue, 22 Mar 2005 00:35:13 +0000 (GMT) Received: (qmail 13585 invoked from network); 22 Mar 2005 01:35:25 +0100 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 22 Mar 2005 01:35:25 +0100 To: "Thomas Borg Salling" , pgsql-sql@postgresql.org Subject: Re: "Flattening" query result into columns References: <20050321215723.E79E356388@svr1.postgresql.org> Message-ID: From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Date: Tue, 22 Mar 2005 01:35:15 +0100 In-Reply-To: <20050321215723.E79E356388@svr1.postgresql.org> User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/292 X-Sequence-Number: 20771 You could use the array_accum aggregate. See examples in the docs. On Mon, 21 Mar 2005 22:57:13 +0100, Thomas Borg Salling wrote: > I am looking for a way to "flatten" a query result, so that rows are > "transposed" into columns, just as asked here for oracle: > > http://groups.google.dk/groups?hl=da > n-US:official&selm=aad10be0.0401292322.7b6c320b%40posting.google.com> > &lr=&client=firefox-a&rls=org.mozilla:en-US:official&selm=aad10be0.040129232 > 2.7b6c320b%40posting.google.com > > > Is there any way to do this with pgsql ? > > > Thanks, > > /Thomas. > > > From smarlowe@g2switchworks.com Tue Mar 22 00:46:18 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0BF0A565F0 for ; Tue, 22 Mar 2005 00:46:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61786-10 for ; Tue, 22 Mar 2005 00:46:16 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 5318056539 for ; Tue, 22 Mar 2005 00:46:15 +0000 (GMT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 21 Mar 2005 18:46:17 -0600 Subject: Re: "Flattening" query result into columns From: Scott Marlowe To: Thomas Borg Salling Cc: pgsql-sql@postgresql.org In-Reply-To: <20050321215723.E79E356388@svr1.postgresql.org> References: <20050321215723.E79E356388@svr1.postgresql.org> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1111452377.26897.487.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 21 Mar 2005 18:46:17 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/294 X-Sequence-Number: 20773 On Mon, 2005-03-21 at 15:57, Thomas Borg Salling wrote: > I am looking for a way to =E2=80=9Dflatten=E2=80=9D a query result, so th= at rows are > =E2=80=9Dtransposed=E2=80=9D into columns, just as asked here for oracle: >=20 > http://groups.google.dk/groups?hl=3Dda&lr=3D&client=3Dfirefox-a&rls=3Dorg= .mozilla:en-US:official&selm=3Daad10be0.0401292322.7b6c320b%40posting.googl= e.com >=20 > =20 >=20 > Is there any way to do this with pgsql ? Here's one from work that allows you to do the same basic thing without a separate cross table: select=20 a.lt , b.perspective as XYZ_pers, b.averageresponsetime as XYZ_aver, b.lowestresponsetime as XYZ_lowe, b.highestresponsetime as XYZ_high, b.totalcount as XYZ_tota, c.perspective as ABC_pers, c.averageresponsetime as ABC_aver, c.lowestresponsetime as ABC_lowe, c.highestresponsetime as ABC_high, c.totalcount as ABC_tota=20 from ( select distinct date_trunc('minutes', lastflushtime) as lt from businessre= questsummary where lastflushtime between '2005-03-14 18:42:34' and '2005-03-21 18:42:34= '=20 and perspective in ('XYZ','ABC') ) as a=20 left join ( select date_trunc('minutes', lastflushtime) as lt, max(perspective) as perspective, floor(avg(averageresponsetime)) as averageresponsetime, min(lowestresponsetime) as lowestresponsetime, max(highestresponsetime) as highestresponsetime, sum(totalcount) as totalcount from businessrequestsummary=20 where perspective =3D'XYZ' group by date_trunc('minutes', lastflushtime) ) as b=20 on=20 (a.lt=3Db.lt)=20 left join ( select date_trunc('minutes', lastflushtime) as lt, max(perspective) as perspective, floor(avg(averageresponsetime)) as averageresponsetime, min(lowestresponsetime) as lowestresponsetime, max(highestresponsetime) as highestresponsetime, sum(totalcount) as totalcount from businessrequestsummary=20 where perspective =3D'ABC' group by date_trunc('minutes', lastflushtime) ) as c=20 on=20 (a.lt=3Dc.lt)=20 IT's generated by a script that makes it as big as we need for all the diff= erent perspectives. From t.matiisen@aprote.ee Wed Mar 23 08:13:28 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CF5185295E for ; Wed, 23 Mar 2005 08:13:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71670-10 for ; Wed, 23 Mar 2005 08:13:18 +0000 (GMT) Received: from ns.aprote.ee (ns.aprote.ee [80.235.78.106]) by svr1.postgresql.org (Postfix) with ESMTP id 53C3F53536 for ; Wed, 23 Mar 2005 08:13:16 +0000 (GMT) Received: Message by Barricade ns.aprote.ee with ESMTP id j2N8EJOj004124 for ; Wed, 23 Mar 2005 10:14:20 +0200 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-4" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 Subject: Re: "Flattening" query result into columns Date: Wed, 23 Mar 2005 10:13:14 +0200 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: "Flattening" query result into columns Thread-Index: AcUvgClN4YkAW8nEQ+2FKrExmwhnZQ== From: "Tambet Matiisen" To: "Leif B. Kristensen" , X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/312 X-Sequence-Number: 20791 The problem in linked article could be solved with custom aggregate in = PostgreSQL: create or replace function concat(text, text) returns text immutable language sql as ' select case when $1 =3D '''' then $2 else $1 || '' '' || $2 end '; drop aggregate concat(text) cascade; create aggregate concat=20 ( basetype =3D text, stype =3D text, sfunc =3D concat, initcond =3D '' ); The query: select Col_A, concat(Col_B) from table group by Col_A If you want only distinct values concatenated: select Col_A, concat(distinct Col_B) from table group by Col_A Tambet > -----Original Message----- > From: Leif B. Kristensen [mailto:leif@solumslekt.org]=20 > Sent: Tuesday, March 22, 2005 12:37 AM > To: pgsql-sql@postgresql.org > Subject: Re: "Flattening" query result into columns >=20 >=20 > On Monday 21 March 2005 22:57, Thomas Borg Salling wrote: > > I am looking for a way to "flatten" a query result, so that=20 > rows are=20 > > "transposed" into columns, just as asked here for oracle: > > > > Is there any way to do this with pgsql =A0? >=20 > Just to help out the guys, here's a working link: >=20 > What you're asking for is called a pivot table, and at least in Oracle=20 they use a function called decode(). I need exactly the same thing, but=20 I too am unable to find the optimal way to do it in PostgreSQL. --=20 Leif Biberg Kristensen http://solumslekt.org/ From tbs@navicon.dk Sun Mar 27 21:10:16 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B7DD6533EF for ; Sun, 27 Mar 2005 22:10:16 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16136-03 for ; Sun, 27 Mar 2005 21:10:06 +0000 (GMT) Received: from navicon.dk (cpe.atm1-0-1091060.0x50a3778a.arcnxe1.customer.tele.dk [80.163.119.138]) by svr1.postgresql.org (Postfix) with ESMTP id 031F0533F0 for ; Sun, 27 Mar 2005 22:10:04 +0100 (BST) Received: from Spartacus (10.0.1.131:3626) by raptus.navicon.dk with [XMail 1.18 (Linux/Ix86) ESMTP Server] id for from ; Sun, 27 Mar 2005 23:10:04 +0200 From: "Thomas Borg Salling" To: Subject: Re: "Flattening" query result into columns Date: Sun, 27 Mar 2005 23:09:50 +0200 Organization: NAVICON A/S MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook, Build 11.0.6353 In-reply-to: <1111452377.26897.487.camel@state.g2switchworks.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcUueUj3AqtKG4ptQyKBhUdUZ9oUugDuj9SQ Message-Id: <20050327211004.031F0533F0@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.589 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200503/362 X-Sequence-Number: 20841 Thanks to all for the useful replies. I chose this approach from Scott Marlowe, which can meet the requirements I work against. Arrays seemed = to have some issues with element with null elements. /Thomas. =20 -----Oprindelig meddelelse----- Fra: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] P=E5 vegne af Scott Marlowe Sendt: 22. marts 2005 01:46 Til: Thomas Borg Salling Cc: pgsql-sql@postgresql.org Emne: Re: [SQL] "Flattening" query result into columns On Mon, 2005-03-21 at 15:57, Thomas Borg Salling wrote: > I am looking for a way to =94flatten=94 a query result, so that rows = are > =94transposed=94 into columns, just as asked here for oracle: >=20 > http://groups.google.dk/groups?hl=3Dda&lr=3D&client=3Dfirefox-a&rls=3Dorg= .mozilla:en -US:official&selm=3Daad10be0.0401292322.7b6c320b%40posting.google.com >=20 > =20 >=20 > Is there any way to do this with pgsql ? Here's one from work that allows you to do the same basic thing without a separate cross table: select=20 a.lt , b.perspective as XYZ_pers, b.averageresponsetime as XYZ_aver, b.lowestresponsetime as XYZ_lowe, b.highestresponsetime as XYZ_high, b.totalcount as XYZ_tota, c.perspective as ABC_pers, c.averageresponsetime as ABC_aver, c.lowestresponsetime as ABC_lowe, c.highestresponsetime as ABC_high, c.totalcount as ABC_tota=20 from ( select distinct date_trunc('minutes', lastflushtime) as lt from businessrequestsummary where lastflushtime between '2005-03-14 18:42:34' and '2005-03-21 18:42:34'=20 and perspective in ('XYZ','ABC') ) as a=20 left join ( select date_trunc('minutes', lastflushtime) as lt, max(perspective) as perspective, floor(avg(averageresponsetime)) as averageresponsetime, min(lowestresponsetime) as lowestresponsetime, max(highestresponsetime) as highestresponsetime, sum(totalcount) as totalcount from businessrequestsummary=20 where perspective =3D'XYZ' group by date_trunc('minutes', lastflushtime) ) as b=20 on=20 (a.lt=3Db.lt)=20 left join ( select date_trunc('minutes', lastflushtime) as lt, max(perspective) as perspective, floor(avg(averageresponsetime)) as averageresponsetime, min(lowestresponsetime) as lowestresponsetime, max(highestresponsetime) as highestresponsetime, sum(totalcount) as totalcount from businessrequestsummary=20 where perspective =3D'ABC' group by date_trunc('minutes', lastflushtime) ) as c=20 on=20 (a.lt=3Dc.lt)=20 IT's generated by a script that makes it as big as we need for all the different perspectives. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings