Re: Crosstab Confusion

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?)

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.

I didn't think about this before, but you can also pull the category headers
from your source table like this:

'SELECT DISTINCT date_trunc(''day'', date) AS day WHERE date >=
''2010-01-01'' ORDER BY 1'

Let me know if this gets you anywhere.

--Lee

On Mon, Feb 1, 2010 at 11:52 AM, Adam Sherman <adam(at)sherman(dot)ca> wrote:

> On 2010-02-01, at 11:34 , Lee Hachadoorian wrote:
> > You basically have three parts:
> >
> > 1) SELECT query in the form (row header, column header, cell value). In
> this case it is an aggregate query so that you can sum the transactions
> over a given day.
> >
> > 2) List of column headers. If you want, this can SELECT from another
> table, so you can have a table with rows 1, 2, 3, etc and use it to select
> the days from the month instead of listing them manually.
> >
> > 3) List of output columns, which follows "pivot" in the text above. Note
> that "pivot" is an arbitrary name. You can use foo, bar, or whatever, but
> that will be the name of the table which must be used to reference the
> columns in the top SELECT list.
>
> Wow that's an incredibly complete response!
>
> I'm not getting any data in my rows though. This query produces the data:
>
> SELECT
> cust_id as customer,
> date_trunc('day', date) AS day,
> SUM(billed_duration) AS minutes
> FROM master_cdr
> WHERE date >= '2010-01-01'
> GROUP BY 1,2
> ORDER BY 1,2;
>
> Which looks like:
>
> customer | day | minutes
> ----------+---------------------+---------
> 1 | 2010-01-01 00:00:00 | 1110
> 1 | 2010-01-03 00:00:00 | 60
> 1 | 2010-01-26 00:00:00 | 23010
> 1 | 2010-01-27 00:00:00 | 17910
> 2 | 2010-01-01 00:00:00 | 60
> 2 | 2010-01-02 00:00:00 | 30
> 2 | 2010-01-04 00:00:00 | 26310
> etc, etc, etc
>
> But this query:
>
> -- clients by day
> SELECT pivot.* FROM crosstab(
> 'SELECT
> cust_id as customer,
> date_trunc(''day'', date) AS day,
> SUM(billed_duration) AS minutes
> FROM master_cdr
> WHERE date >= ''2010-01-01''
> GROUP BY 1,2
> ORDER BY 1,2',
> 'select * from day_of_month'
> ) pivot (
> customer integer,
> day1 numeric(10,4),
> day2 numeric(10,4),
> (…)
> day31 numeric(10,4)
> )
> ORDER BY customer;
>
> Gives me a table that looks right but all values are null for the days.
>
> Something simple maybe?
>
> 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
>
>

--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center