Cross Tab Functions

From: "Chris Preston" <chris(at)thetrafalgartravel(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Cross Tab Functions
Date: 2008-06-20 13:05:48
Message-ID: 200806201305.m5KD5U1L018528@mail2.trafalgar.lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello All,
I have used the cross tab function to setup tables that display months'
data, however i need to display years data instead of the months.. so i
would display 2006 as a column, 2007 as a colum and 2008 as a column. when i
tried to modify the simple example of the cross tab (shown below)
SELECT i.item_name::text As row_name, to_char(if.action_date, 'year')::text
As bucket,
SUM(if.num_used)::integer As bucketvalue
FROM inventory As i INNER JOIN inventory_flow As if
ON i.item_id = if.item_id
WHERE (if.num_used <> 0 AND if.num_used IS NOT NULL)
AND action_date BETWEEN date '2007-01-01' and date '2008-12-31 23:59'
GROUP BY i.item_name, to_char(if.action_date, 'year'), date_part('year',
if.action_date)
ORDER BY i.item_name, date_part('year', if.action_date);

basically i changed the mon and the month to be year.. the system displayed
something that i am not sure about. is there a command that converts the
year similar to the mon

Help again...Please

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2008-06-20 14:02:14 Re: "TZ"/"tz" not supported
Previous Message Tk421 2008-06-20 12:20:18 Problem inserting data