From: Adam Sherman <adam@sherman.ca>
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Crosstab Confusion
Date: Mon, 1 Feb 2010 11:52:16 -0500
Message-ID: <8ACF8F3F-F2DA-4244-886F-6C65F8D1C600@sherman.ca> (raw)
In-Reply-To: <5ab13581002010834k11c1be7ci4b4aef1b6778be4f@mail.gmail.com>
References: <C5C50165-6162-460C-8E13-A0A76D0B726B@sherman.ca>
<5ab13581002010834k11c1be7ci4b4aef1b6778be4f@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adam@sherman.ca, lee.hachadoorian@gmail.com
Subject: Re: Crosstab Confusion
In-Reply-To: <8ACF8F3F-F2DA-4244-886F-6C65F8D1C600@sherman.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox