On Mon, Feb 1, 2010 at 3:11 PM, Adam Sherman
<adam@sherman.ca> wrote:
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?
If billed_duration is an integer, sum(billed_duration) will be int or bigint. I would just define the output columns as bigint (day1 bigint, day2 bigint, etc.). Although, formatting it in the source SQL (I see you are dividing by 60 and casting to numeric) saves you from having to format 31 output columns.
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.
Not that I know of, but I have confirmed that as long as the category SQL matches the output column list, you can have output columns with no data in them. In order to have your list of column headers match the source SQL, I would recommend going back to the extract() function I first recommended to extract the day of month as an integer, and then generate a 31 number series for your category headers. It would look like this:
SELECT pivot.* FROM crosstab(
'SELECT
cust_id as customer,
extract(day from date)::integer AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE extract(month FROM date) = 1
GROUP BY 1,2
ORDER BY 1,2',
'select day from generate_series(1,31) day'
) pivot (
customer integer,
day1 bigint,
day2 bigint,
(…)
day31 bigint
)
ORDER BY customer;
For half-over months or months with fewer than 31 days, the final columns of the crosstab should just be blank.
PS: The way I have constructed it, I would avoid using WHERE date >= ''2010-01-01''. If data from February gets into the table, it will aggregate data from, e.g. Jan 2 and Feb 2 as both being part of "Day 2".
--Lee
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center