On 2010-02-01, at 14:22 , Lee Hachadoorian wrote:
> The output column data type (day1, day2, etc.) is supposed to match the value
> data type. I used numeric(10,4) because that's what your original post
> specified, but the billed_duration column in your most recent post looks like
> it might be integer? (Or is it defined as numeric(10,4), but you never enter
> noninteger values?)
Actually, the query I was running is:
SELECT
cust_id as customer,
date_trunc(''day'', date) AS day,
SUM(billed_duration)/60.0::numeric(10,4) AS minutes
billed_duration is an integer. Make sense?
> What's the output of the category query by itself? I forgot to include ORDER
> BY 1 at the end of the category query. (The order should match the order of
> output columns, but I think without it you wouldn't get NULL values, just the
> values would be in the wrong columns.) I assume day_of_month has only one
> column, but I would suggest naming it explicitly instead of using *. And is
> the day_of_month column defined in the same format as date_trunc('day',
> date)? They must successfully pass an "equals" test to get included in the
> right crosstab cell. If a category value in the source query doesn't match
> any value produced by the category query, I think the crosstab function just
> throws out that row, which could lead to a table with the correct structure
> but all NULLs.
Right, my list of columns weren't equal to the truncated date. Using your
suggested query to generate the columns fixed the problem!
Now, is there a way to generate the labels? Otherwise I have to adjust the
query for th number of days returned.
Such nice output though! Awesome!
Thanks,
A.
--
www.sherman.ca / +1-613-797-6819 / +1-646-233-3400
"When the burning husks of your startups warm the last of your bones, remember
I told you so." - Zed
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
