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--