Try using extract instead of to_char. date_trunc might also be of use.
On Jun 20, 2008, at 8:05 AM, Chris Preston wrote:
> 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
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828