Re: Crosstab Confusion

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