Subject: Re: Crosstab Confusion
Mime-Version: 1.0 (Apple Message framework v1077)
Content-Type: text/plain; charset=windows-1252
From: Adam Sherman <adam@sherman.ca>
In-Reply-To: <5ab13581002010834k11c1be7ci4b4aef1b6778be4f@mail.gmail.com>
Date: Mon, 1 Feb 2010 11:52:16 -0500
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <8ACF8F3F-F2DA-4244-886F-6C65F8D1C600@sherman.ca>
References: <C5C50165-6162-460C-8E13-A0A76D0B726B@sherman.ca>
	<5ab13581002010834k11c1be7ci4b4aef1b6778be4f@mail.gmail.com>
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>

On 2010-02-01, at 11:34 , Lee Hachadoorian wrote:
> You basically have three parts:
>=20
> 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.
>=20
> 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.
>=20
> 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 >=3D '2010-01-01'
GROUP BY 1,2
ORDER BY 1,2;

Which looks like:

 customer |         day         | minutes=20
----------+---------------------+---------
        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 >=3D ''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),
(=85)
  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.

--=20
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