pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robert Fitzpatrick <lists@webtent.net>
To: PostgreSQL <pgsql-general@postgresql.org>
Subject: Crosstab
Date: Tue, 20 Feb 2007 18:44:31 -0500
Message-ID: <1172015071.9395.18.camel@columbus.webtent.org> (raw)

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




view thread (25+ messages)  latest in thread

Message-ID: <1172015071.9395.18.camel@columbus.webtent.org>
Permalink:  ../1172015071.9395.18.camel@columbus.webtent.org/
Also on:    postgresql.org/message-id/1172015071.9395.18.camel@columbus.webtent.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: lists@webtent.net
  Subject: Re: Crosstab
  In-Reply-To: <1172015071.9395.18.camel@columbus.webtent.org>
  References: <1172015071.9395.18.camel@columbus.webtent.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox