-------------------------------------------------------

aaa &nb= sp;=20 2 = ;=20 2003-04-01

bbb &nb= sp;=20 4 = ;=20 2003-04-12

ccc &nb= sp;=20 5 = ;=20 2003-05-12

aaa &nb= sp;=20 3 = ;=20 2003-01-14

aaa &nb= sp;=20 1 &n= bsp; =20 2003-12-09

bbb &nb= sp;=20 9 = ;=20 2003-08-08

and so on ...

Does=20 anybody know how make query which return grouped

records by month of year an= d name=20 (also sum of quantity).

It is possible to make a query whitch= return=20 something like that:

name | 01 | 02 | 03 | 04 | ... | 12= =20 (months)

------------------------------------------------

aaa =20 x x x = ;=20 x ... x

bbb =20 x x x = ;=20 x ... x

ccc =20 x x x = ;=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_00C7_01C3D970.692DCE40-- From jim.bagley@traderonline.com Fri Jan 16 18:10:47 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7097AD1B4D7 for ; Fri, 16 Jan 2004 18:10:47 +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 55838-07 for ; Fri, 16 Jan 2004 14:10:17 -0400 (AST) Received: from neomail03.traderonline.com (unknown [65.213.231.7]) by svr1.postgresql.org (Postfix) with ESMTP id 0B74AD1B4A4 for ; Fri, 16 Jan 2004 14:10:16 -0400 (AST) Received: from traderonline.com ([10.2.1.141]) by neomail03.traderonline.com (8.12.8/8.12.8) with ESMTP id i0GIABVr026184; Fri, 16 Jan 2004 13:10:11 -0500 Message-ID: <40082903.2060909@traderonline.com> Date: Fri, 16 Jan 2004 13:10:11 -0500 From: "jim.bagley@traderonline.com" Reply-To: jim.bagley@traderonline.com User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bronx Cc: pgsql-admin@postgresql.org Subject: Re: Is it possible in PostgreSQL? References: <00ca01c3d968$07c05fe0$0201a8c0@bronx> In-Reply-To: <00ca01c3d968$07c05fe0$0201a8c0@bronx> Content-Type: multipart/alternative; boundary="------------090502010005070301030801" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/166 X-Sequence-Number: 12029 This is a multi-part message in MIME format. --------------090502010005070301030801 Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit In Oracle you can use cursors as columns. not sure if that will work in postgresql. never tried it. =) ex: select name, curosr(select sum(quantity) from tablename where date between(pick a date) and (pick another date)) as 01, curosr(select sum(quantity) from tablename where date between(pick a date) and (pick another date)) as 02, curosr(select sum(quantity) from tablename where date between(pick a date) and (pick another date)) as 03, from tablename; thanks should give you the output you described below. Again, I am not sure if this is SQL Standard or just a function that Oracle offers. jim Bronx wrote: > 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 > records by month of year and name (also sum of quantity). > 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 > bbb x x x x ... x > 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 > Adam > -- Jim Bagley Database Manager Trader Electronic Media (757) 321-8409 (work) (757) 343-1768 (cell) www.Employmentguide.com - Bringing Employers and Jobseekers Together. www.ForRent.com - Nation's Leading Apartment Rental Source. www.HarmonHomes.com - Your Home Site on the Web. www.ParenthoodWeb.com - Pregnancy and Parenting Advice, Baby Names and More. www.Roomsaver.com - 6000+ Lodging Coupons. www.TraderOnline.com - Boats, RVs, Collector Cars, Motorcycles and More. --------------090502010005070301030801 Content-Type: text/html; charset=ISO-8859-2 Content-Transfer-Encoding: 8bit In Oracle you can use cursors as columns. not sure if that will work in postgresql. never tried it. =)

ex:

select name, curosr(select sum(quantity) from tablename where date between(pick a date) and (pick another date)) as 01,
curosr(select sum(quantity) from tablename where date between(pick a date) and (pick another date)) as 02,
curosr(select sum(quantity) from tablename where date between(pick a date) and (pick another date)) as 03,
from tablename;

thanks should give you the output you described below. Again, I am not sure if this is SQL Standard or just a function that Oracle offers.

jim

Bronx wrote:

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

records by month of year and name (also sum of quantity).

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

bbb     x     x     x    x    ...   x
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

Adam

--
signature

Jim Bagley
Database Manager
Trader Electronic Media
(757) 321-8409 (work)
(757) 343-1768 (cell)

www.Employmentguide.com - Bringing Employers and Jobseekers Together.
www.ForRent.com - Nation's Leading Apartment Rental Source.
www.HarmonHomes.com - Your Home Site on the Web.
www.ParenthoodWeb.com - Pregnancy and Parenting Advice, Baby Names and More.
www.Roomsaver.com - 6000+ Lodging Coupons.
www.TraderOnline.com - Boats, RVs, Collector Cars, Motorcycles and More.

--------------090502010005070301030801-- From chris@travelamericas.com Sun Jan 18 14:15:38 2004 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

To: pgsql-admin@postgresql.org=

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-- From jjsa@gvtc.com Mon Jan 19 00:11:39 2004 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 8221FD1B91A for ; Mon, 19 Jan 2004 00:11:39 +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 78721-08 for ; Sun, 18 Jan 2004 20:11:11 -0400 (AST) Received: from smtp.gvtc.com (smtp2.gvtc.com [216.177.160.81]) by svr1.postgresql.org (Postfix) with ESMTP id 08C10D1B4AA for ; Sun, 18 Jan 2004 20:11:07 -0400 (AST) Received: from 192.168.1.102 (adsl-64-238-159-220.nrp3-smvy.stx-dhcp.dsl.gvtc.com [64.238.159.220]) by smtp.gvtc.com (8.12.8/8.12.5) with ESMTP id i0J15uBY012161 for ; Sun, 18 Jan 2004 19:05:57 -0600 From: Jim Johannsen To: pgsql-sql@postgresql.org Subject: Re: Is it possible in PostgreSQL? Date: Mon, 19 Jan 2004 18:13:20 -0600 User-Agent: KMail/1.5.4 References: <00ca01c3d968$07c05fe0$0201a8c0@bronx> <019001c3ddcd$74de3660$8d285e3d@winxp> In-Reply-To: <019001c3ddcd$74de3660$8d285e3d@winxp> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200401191813.20683.jjsa@gvtc.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/156 X-Sequence-Number: 16549 This is what I use to flatten a table, the syntax may not be postgresql correct but you will get idea. SELECT a.name ,SUM (CASE WHEN EXTRACT(month from a.date) = 1 THEN a.quantity ELSE 0 END) AS '01' ,SUM(CASE WHEN EXTRACT(month from a.date) = 2 THEN a.quantity ELSE 0 END) AS '02' etc,etc. FROM (SELECT DISTINCT name FROM "whatever") as a JOIN "whatever" as b on a.name = b.name WHERE -- put in year range GROUP BY a.name The "a" table could b a temp table with the know values to speed up execution. The main thing is to only scan the table once. Let me know how it works out for you. On Sunday 18 January 2004 07:02, you wrote: > Moving thread over to SQL list as it belongs there. > > Bronx: This certainly is possible, but IMO, not in one query. Actually > doing it will be relatively complex. For purposes of maintenance, I am > thinking that doing this would be better handled by wrapping at least one > view. > > CREATE VIEW sales_pre_proc AS > 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 > understand 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 help. > > Failing that, you can write your own function to return each row. I was > working on a quick proof of concept but it was not working properly. > > Best Wishes, > Chris Travers > > ----- Original Message ----- > From: Bronx > To: pgsql-admin@postgresql.org > 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 > records by month of year and name (also sum of quantity). > 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 > bbb x x x x ... x > 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 > Adam