X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8D74AD1DF90 for ; Sun, 18 Jan 2004 14:15:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 95334-07 for ; Sun, 18 Jan 2004 10:15:11 -0400 (AST) Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64]) by svr1.postgresql.org (Postfix) with ESMTP id D42DAD1B447 for ; Sun, 18 Jan 2004 10:15:06 -0400 (AST) Received: from [61.5.68.112] (helo=winxp) by noon.pghoster.com with asmtp (Exim 4.24) id 1AiDhi-0007ET-Ua; Sun, 18 Jan 2004 08:15:08 -0600 Message-ID: <019001c3ddcd$74de3660$8d285e3d@winxp> From: "Chris Travers" To: "Bronx" , References: <00ca01c3d968$07c05fe0$0201a8c0@bronx> Subject: Re: Is it possible in PostgreSQL? Date: Sun, 18 Jan 2004 20:02:43 +0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0181_01C3DDFE.092C8490" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - noon.pghoster.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - travelamericas.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_40_50, HTML_FONT_FACE_ODD, REFERENCES X-Spam-Level: X-Archive-Number: 200401/141 X-Sequence-Number: 16534 This is a multi-part message in MIME format. ------=_NextPart_000_0181_01C3DDFE.092C8490 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Moving thread over to SQL list as it belongs there. Bronx: This certainly is possible, but IMO, not in one query. Actually do= ing it will be relatively complex. For purposes of maintenance, I am think= ing that doing this would be better handled by wrapping at least one view. CREATE VIEW sales_pre_proc AS=20 SELECT name, quantity, to_char("date", 'YYYY') AS year, to_char("date", 'MM= ') FROM sales; This is needed for the group by statement below to function properly: CREATE VIEW sales_month_summary AS SELECT name, sum(quantity) AS quantity, year, month from sales_pre_proc GROUP BY name, year, month; This will give you a view that will have the sum information. Now we just = have to create the statement which will create the pivot effect. I underst= and that there is something under contrib/tablefunc for this, but I do not = have it on my system (cygwin), at the moment. Perhaps someone else can hel= p.=20=20 Failing that, you can write your own function to return each row. I was wo= rking on a quick proof of concept but it was not working properly. Best Wishes, Chris Travers ----- Original Message -----=20 From: Bronx=20 To: pgsql-admin@postgresql.org=20 Sent: Tuesday, January 13, 2004 6:58 AM Subject: [ADMIN] Is it possible in PostgreSQL? Hi, I've got problem with one specific query. I've got the table with many of rekords like these: name | quantity | date ------------------------------------------------------- aaa 2 2003-04-01 bbb 4 2003-04-12 ccc 5 2003-05-12 aaa 3 2003-01-14 aaa 1 2003-12-09 bbb 9 2003-08-08 and so on ... Does anybody know how make query which return grouped=20 records by month of year and name (also sum of quantity).=20 It is possible to make a query whitch return something like that: name | 01 | 02 | 03 | 04 | ... | 12 (months) ------------------------------------------------ aaa x x x x ... x=20 bbb x x x x ... x=20 ccc x x x x ... x where x means sum of quantity in month. It is possible to make it in one query? I know that in Access is construction : PIVOT. Thanks=20 Adam ------=_NextPart_000_0181_01C3DDFE.092C8490 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable
Moving thread over to SQL list as it belon= gs=20 there.
 
Bronx:  This certainly is possible, b= ut IMO,=20 not in one query.  Actually doing it will be relatively complex. = For=20 purposes of maintenance, I am thinking that doing this would be better hand= led=20 by wrapping at least one view.
 
CREATE VIEW sales_pre_proc AS =
SELECT name, quantity, to_char("date", 'YY= YY') AS=20 year, to_char("date", 'MM') FROM sales;
 
This is needed for the group by statement= =20 below to function properly:
CREATE VIEW sales_month_summary AS<= /DIV>
SELECT name, sum(quantity) AS quantity, ye= ar, month=20 from sales_pre_proc
GROUP BY name, year, month;
 
This will give you a view that will h= ave the=20 sum information.  Now we just have to create the statement which = will=20 create the pivot effect.  I understand that there is something under= =20 contrib/tablefunc for this, but I do not have it on my system (cygwin)= , at=20 the moment.  Perhaps someone else can help.  
 
Failing that, you can write your own funct= ion to=20 return each row.  I was working on a quick proof of concept but it was= not=20 working properly.
 
Best Wishes,
Chris Travers
 
----- Original Message -----
Fro= m:=20 Bronx
Sent: Tuesday, January 13, 2004 6:= 58=20 AM
Subject: [ADMIN] Is it possible in= =20 PostgreSQL?

Hi,
I've got problem with one specific = query.=20 I've got the table
with many of rekords like=20 these:
 
name    =20 |  quantity    | date
-------------------------------------------------------
aaa      &= nbsp;=20     2          &nb= sp;=20     2003-04-01
bbb      &= nbsp;=20     4          &nb= sp;=20     2003-04-12
ccc      &= nbsp;=20     5          &nb= sp;=20     2003-05-12
aaa      &= nbsp;=20     3          &nb= sp;=20     2003-01-14
aaa          &= nbsp;=20 1            =    =20 2003-12-09
bbb          &= nbsp;=20 9              &nb= sp;=20 2003-08-08
 
and so on ...
 
Does=20 anybody know how make query which return grouped
records by month of year = and name=20 (also sum of quantity).
It is possible to make a query whit= ch return=20 something like that:
 
name | 01 | 02 | 03 | 04 | ... | 12= =20 (months)
------------------------------------------------
aaa    =20 x     x     x  &nb= sp;=20 x    ...   x=20
bbb    =20 x     x     x  &nb= sp;=20 x    ...   x=20
ccc    =20 x     x     x  &nb= sp;=20 x    ...   x
 
where x means sum of quantity in month.
It is possible to make it in one query?
I know that in Access is construction : PIVOT.
 
Thanks
Adam
 
------=_NextPart_000_0181_01C3DDFE.092C8490--