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

Re: Is it possible in PostgreSQL?

From: "Chris Travers" <chris(at)travelamericas(dot)com>
To: "Bronx" <tobronx(at)go2(dot)pl>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Is it possible in PostgreSQL?
Date: 2004-01-18 13:02:43
Message-ID: 019001c3ddcd$74de3660$8d285e3d@winxp (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-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(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

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2004-01-18 18:54:12
Subject: Re: error on build from source
Previous:From: kdykeDate: 2004-01-18 10:44:45
Subject: Re: error on build from source

pgsql-sql by date

Next:From: Richard PooleDate: 2004-01-18 21:57:14
Subject: Re: Left joins with multiple tables
Previous:From: Yuri GordienkoDate: 2004-01-17 07:58:10
Subject: count(*) from cursor

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