Skip site navigation (1) Skip section navigation (2)

Re: Is it possible in PostgreSQL?

From: Jim Johannsen <jjsa(at)gvtc(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Is it possible in PostgreSQL?
Date: 2004-01-20 00:13:20
Message-ID: 200401191813.20683.jjsa@gvtc.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-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(at)postgresql(dot)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


In response to

pgsql-admin by date

Next:From: nilesh GhoneDate: 2004-01-20 05:34:10
Subject: Installation Problem on windows.
Previous:From: Arun GananathanDate: 2004-01-19 18:40:21
Subject: Re: popstgresql query

pgsql-sql by date

Next:From: V i s h a l Kashyap @ [Sai Hertz And Control Systems]Date: 2004-01-20 05:27:23
Subject: Re: Trigger to identify which column(s) updated
Previous:From: Robert CreagerDate: 2004-01-19 17:38:53
Subject: Re: How can I get the last element out of GROUP BY sets?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group