pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs it possible in PostgreSQL?
4+ messages / 4 participants
[nested] [flat]
* Is it possible in PostgreSQL?
@ 2004-01-12 23:58 Bronx <tobronx@go2.pl>
2004-01-16 18:10 ` Re: Is it possible in PostgreSQL? jim.bagley@traderonline.com <jim.bagley@traderonline.com>
2004-01-18 13:02 ` Re: Is it possible in PostgreSQL? Chris Travers <chris@travelamericas.com>
0 siblings, 2 replies; 4+ messages in thread
From: Bronx @ 2004-01-12 23:58 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Is it possible in PostgreSQL?
2004-01-12 23:58 Is it possible in PostgreSQL? Bronx <tobronx@go2.pl>
@ 2004-01-16 18:10 ` jim.bagley@traderonline.com <jim.bagley@traderonline.com>
1 sibling, 0 replies; 4+ messages in thread
From: jim.bagley@traderonline.com @ 2004-01-16 18:10 UTC (permalink / raw)
To: Bronx <tobronx@go2.pl>; +Cc: pgsql-admin@postgresql.org
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 <http://www.employmentguide.com; - Bringing
Employers and Jobseekers Together.
www.ForRent.com <http://www.forrent.com; - Nation's Leading Apartment
Rental Source.
www.HarmonHomes.com <http://www.harmonhomes.com; - Your Home Site on the
Web.
www.ParenthoodWeb.com <http://www.parenthoodweb.com; - Pregnancy and
Parenting Advice, Baby Names and More.
www.Roomsaver.com <http://www.roomsaver.com; - 6000+ Lodging Coupons.
www.TraderOnline.com <http://www.traderonline.com; - Boats, RVs,
Collector Cars, Motorcycles and More.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Is it possible in PostgreSQL?
2004-01-12 23:58 Is it possible in PostgreSQL? Bronx <tobronx@go2.pl>
@ 2004-01-18 13:02 ` Chris Travers <chris@travelamericas.com>
2004-01-20 00:13 ` Re: Is it possible in PostgreSQL? Jim Johannsen <jjsa@gvtc.com>
1 sibling, 1 reply; 4+ messages in thread
From: Chris Travers @ 2004-01-18 13:02 UTC (permalink / raw)
To: Bronx <tobronx@go2.pl>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Is it possible in PostgreSQL?
2004-01-12 23:58 Is it possible in PostgreSQL? Bronx <tobronx@go2.pl>
2004-01-18 13:02 ` Re: Is it possible in PostgreSQL? Chris Travers <chris@travelamericas.com>
@ 2004-01-20 00:13 ` Jim Johannsen <jjsa@gvtc.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jim Johannsen @ 2004-01-20 00:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-01-20 00:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-12 23:58 Is it possible in PostgreSQL? Bronx <tobronx@go2.pl>
2004-01-16 18:10 ` jim.bagley@traderonline.com <jim.bagley@traderonline.com>
2004-01-18 13:02 ` Chris Travers <chris@travelamericas.com>
2004-01-20 00:13 ` Jim Johannsen <jjsa@gvtc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox