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
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
