Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1EDCD9FB90C for ; Tue, 20 Feb 2007 19:47:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 65048-01 for ; Tue, 20 Feb 2007 19:47:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from esmtp.webtent.net (esmtp.webtent.net [208.38.145.5]) by postgresql.org (Postfix) with ESMTP id E0B059FB857 for ; Tue, 20 Feb 2007 19:47:11 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by esmtp.webtent.net (WebTent ESMTP Postfix Internet Mail Gateway) with ESMTP id 333907E8D7 for ; Tue, 20 Feb 2007 18:47:11 -0500 (EST) X-Virus-Scanned: amavisd-new at webtent.net Received: from esmtp.webtent.net ([127.0.0.1]) by localhost (esmtp.webtent.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 8XIHhUyeh5UU for ; Tue, 20 Feb 2007 18:47:04 -0500 (EST) Received: from columbus.webtent.org (ftp.webtent.org [70.110.70.43]) by esmtp.webtent.net (WebTent ESMTP Postfix Internet Mail Gateway) with ESMTP id 78F1C7EC93 for ; Tue, 20 Feb 2007 18:47:04 -0500 (EST) Subject: Crosstab From: Robert Fitzpatrick To: PostgreSQL Content-Type: text/plain Organization: WebTent Networking, Inc. Date: Tue, 20 Feb 2007 18:44:31 -0500 Message-Id: <1172015071.9395.18.camel@columbus.webtent.org> Mime-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.034 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/1088 X-Sequence-Number: 109578 I am trying to use the crosstab function of the contrib tablefunc. Reading the README, I believe I am supposed to be using crosstab(sql, N) for my situation and wondering if the SQL can be based on a view? I have this view created that gives me each sales rep and their total number of units sold and total revenue for each month: CREATE VIEW "public"."view_pick1" ( rep, month, units, revenue) AS SELECT users.user_login AS rep, date_part('month'::text, current_clients.start_date) AS "month", count(companies.company_id) AS units, sum(companies.company_revenue) AS revenue FROM ((companies JOIN current_clients ON ((companies.company_id = current_clients.client_id))) JOIN users ON ((companies.company_sales_rep = users.user_id))) GROUP BY users.user_login, date_part('month'::text, current_clients.start_date) ORDER BY users.user_login, date_part('month'::text, current_clients.start_date); Trying to make a crosstab, let's say just for units, this is what I'm attempting, which is wrong of course, can someone enlighten me as this is my first crosstab. select * from crosstab('select rep, month, units from view_pick1 order by 1,2;', 12) AS view_pick1(rep varchar, jan double precision, feb double precision, mar double precision, apr double precision, may double precision, jun double precision, jul double precision, aug double precision, sep double precision, oct double precision, nov double precision, dec double precision); Error is: ERROR: return and sql tuple descriptions are incompatible SQL state: 42601 Not sure what that means, I tried to match up the view field types with the returned fields. My sql produces the following after which is what I would like to get. Am I even going about this correctly? rep | month | units --------------+-------+------- aespinal | 5 | 4 aespinal | 6 | 3 asmith | 1 | 1 athranow | 1 | 5 athranow | 2 | 1 athranow | 3 | 2 athranow | 4 | 1 rep jan feb mar apr may jun etc... ---------+-----+-----+-----+-----+-----+-----+- aespinal 4 3 asmith 1 athranow 5 1 2 1 Thanks for the help! -- Robert