Re: Crosstab Confusion

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