From: Jim Johannsen <jjsa@gvtc.com>
To: pgsql-sql@postgresql.org
Subject: Re: Is it possible in PostgreSQL?
Date: Mon, 19 Jan 2004 18:13:20 -0600
Message-ID: <200401191813.20683.jjsa@gvtc.com> (raw)
In-Reply-To: <019001c3ddcd$74de3660$8d285e3d@winxp>
References: <00ca01c3d968$07c05fe0$0201a8c0@bronx>
<019001c3ddcd$74de3660$8d285e3d@winxp>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jjsa@gvtc.com
Subject: Re: Is it possible in PostgreSQL?
In-Reply-To: <200401191813.20683.jjsa@gvtc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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