From gibsosmat@gmail.com Wed Jun  24 16:39:26 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id AC4C06331D1
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 24 Jun 2009 13:39:26 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 45875-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 24 Jun 2009 13:39:23 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from rv-out-0506.google.com (rv-out-0506.google.com
 [209.85.198.229])
	by mail.postgresql.org (Postfix) with ESMTP id C54E5631ADA
	for <pgsql-sql@postgresql.org>; Wed, 24 Jun 2009 13:39:24 -0300 (ADT)
Received: by rv-out-0506.google.com with SMTP id f9so139121rvb.43
	for <pgsql-sql@postgresql.org>; Wed, 24 Jun 2009 09:39:23 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:subject:from:reply-to:to
	:content-type:date:message-id:mime-version:x-mailer;
	bh=PW1BKQXCQtQUmmV+qkeukO8/2cPa/bfHO/KGdbzEjNY=;
	b=nMQ0B40nFRHwtyvWQKkPIK6Lg4n6lDv14QSf35mc+XnGJ4ol3Tgw04aypW5Ls4FmQ5
	GapbREtY5giA9JJHuQp4FkixFS9n5urVr6izwY1gOO5IzHjEUL34n6sIl7X91Z+Rf1Gk
	3Ig1V15Adws/2aoBq1IvgQ8V3M/joQqpsAcrw=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:from:reply-to:to:content-type:date:message-id:mime-version
	:x-mailer;
	b=shv3992putNG/Nx6ky+Coid6ULjcdf9fMi/5DhXkeGnhj1q/VUCpisI3s+XrK89u4k
	KoELeWC65rvhwfknx6RTot0ZWKrBzUG4qaURAv4BJ/s5gYnEMhyqhwxklbGOAsum279F
	ERFbYPJBljBcciV1LVbVvgfeCMWIRKuqxwM7k=
Received: by 10.141.48.10 with SMTP id a10mr743734rvk.204.1245861563596;
	Wed, 24 Jun 2009 09:39:23 -0700 (PDT)
Received: from ?192.168.0.100? ([122.167.39.0])
	by mx.google.com with ESMTPS id b39sm5511589rvf.6.2009.06.24.09.39.21
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Wed, 24 Jun 2009 09:39:22 -0700 (PDT)
Subject: Bucketing Row Data in columns
From: Sandeep <gibsosmat@gmail.com>
Reply-To: gibsosmat@gmail.com
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary="=-QvPUN8+HNG3ky/nUaspu"
Date: Wed, 24 Jun 2009 22:09:15 +0530
Message-Id: <1245861555.4420.30.camel@devil>
Mime-Version: 1.0
X-Mailer: Evolution 2.26.1 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/100
X-Sequence-Number: 32705


--=-QvPUN8+HNG3ky/nUaspu
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hi all,
I need help on creating a sql, not a problem even if its pl/sql

I have orders table schema is as follow

orders(order_id,user_id, create_timestamp, amount)

and I want to generate a report like
for the past 3 days bucketing purchases i.e SUM(amount) every day in
columns
i.e result will be having these columns.

(user_id, amount_day1, amount_day2, amount_day3)

ex:
am leaving order_id assume they are auto incrementing and unique, date
format dd/mm/yyyy
(user_id, create_timestamp, amount)
(user1, 01/01/2009,100)
(user1, 01/01/2009,100)
(user2, 01/01/2009,100)
(user2, 02/01/2009,100)
(user2, 02/01/2009,100)
(user1, 02/01/2009,100)
(user2, 03/01/2009,100)
(user2, 03/01/2009,100)
(user3, 03/01/2009,100)


result

(user_id, amount_day1, amount_day2, amount_day3)
(user1, 200, 200, 0)
(user2, 100, 200, 200)
(user3, 0, 0, 100)


hope you guys got what I am trying to generate through sql.

I could get this data in each row, but I want it in columns.
Can anyone help me on this? lets assume the buckets are fixed i.e 3
only. but I wish to get them unlimited i.e day 1 to day 20.

Regards
Sandeep Bandela

--=-QvPUN8+HNG3ky/nUaspu
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.26.0">
</HEAD>
<BODY>
Hi all,<BR>
I need help on creating a sql, not a problem even if its pl/sql<BR>
<BR>
I have orders table schema is as follow<BR>
<BR>
orders(order_id,user_id, create_timestamp, amount)<BR>
<BR>
and I want to generate a report like<BR>
for the past 3 days bucketing purchases i.e SUM(amount) every day in columns<BR>
i.e result will be having these columns.<BR>
<BR>
(user_id, amount_day1, amount_day2, amount_day3)<BR>
<BR>
ex:<BR>
am leaving order_id assume they are auto incrementing and unique, date format dd/mm/yyyy<BR>
(user_id, create_timestamp, amount)<BR>
(user1, 01/01/2009,100)<BR>
(user1, 01/01/2009,100)<BR>
(user2, 01/01/2009,100)<BR>
(user2, 02/01/2009,100)<BR>
(user2, 02/01/2009,100)<BR>
(user1, 02/01/2009,100)<BR>
(user2, 03/01/2009,100)<BR>
(user2, 03/01/2009,100)<BR>
(user3, 03/01/2009,100)<BR>
<BR>
<BR>
result<BR>
<BR>
(user_id, amount_day1, amount_day2, amount_day3)<BR>
(user1, 200, 200, 0)<BR>
(user2, 100, 200, 200)<BR>
(user3, 0, 0, 100)<BR>
<BR>
<BR>
hope you guys got what I am trying to generate through sql.<BR>
<BR>
I could get this data in each row, but I want it in columns.<BR>
Can anyone help me on this? lets assume the buckets are fixed i.e 3 only. but I wish to get them unlimited i.e day 1 to day 20.<BR>
<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
Regards<BR>
Sandeep Bandela
</TD>
</TR>
</TABLE>
</BODY>
</HTML>

--=-QvPUN8+HNG3ky/nUaspu--




From jameskitambara@yahoo.co.uk Thu Jun  25 08:56:01 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id B25A763575B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 05:56:01 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 59547-01-7 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 05:55:55 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from web27906.mail.ukl.yahoo.com (web27906.mail.ukl.yahoo.com
	[217.146.182.56])
	by mail.postgresql.org (Postfix) with SMTP id AE4956351EE
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 05:55:54 -0300 (ADT)
Received: (qmail 1850 invoked by uid 60001); 25 Jun 2009 08:55:51 -0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024;
	t=1245920151; bh=RYqni+7Fp7W8MUR5jTI9ur2+eB2A4IrK/jLalR7rql8=;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type;
	b=iVeQ/thaUaXCeLjMCeX02JevH6aTmsWLq/zms6iYY57+okXGL/tI03VH/COSwTSu991kN9PK7nWbLtXEVoN/C2qtXwPR9QBKpJ8qUQneG0hf93HBi0RCU3ipI+h8l3maOxkxE2mBPZ65BoPj1sNQhUfUvkIUZK9+AlZ921XAUDk=
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type;
	b=Gr60LAKStWPJAUFPoy0v0ZZjQhc48Y8Uj4K36dzzgEep1ARCKhoLzou6xchQQf7EGxXTUYsAo8a2+3JoxbOnmjKX81ml7vi+jGrRZGEepZjTnEOFc5LmCgiTAQmiXwkGVK8cCp51w7r1BTPhstTXTYQSlNx5QYeV6NyCSa878Dc=;
Message-ID: <863824.473.qm@web27906.mail.ukl.yahoo.com>
X-YMail-OSG: 
 O8G.zAMVM1m6Lgn1Ct0DYjpQqUpI2XnxW3YY52pMcI3JJVqzT.bdMg_yFHknVPHPk7WAYs237IPqlxRTm00KRs_ELkx8CT_b5QW70fyIrbSNBzQHidL6jKxhVuoBtwGieOxhkK034TKeP0cN6BkL0jql6GWyfcX4MQMhNbvttm8b1.tQ2D_xEUFIReTe1BPVfJX6KM5tTwj6KrKDfzPkPfJXkLL3U8SADbkWWu__jdvAQZD8zR4eTbOi43Hb.5gpP1iEJeHlqFxXCOxxaIWIoO.TxG0_csZNu8VUVtY5jdhtTlSBC06Q9EVzfcpCYtBk51U-
Received: from [196.43.80.43] by web27906.mail.ukl.yahoo.com via HTTP;
	Thu, 25 Jun 2009 08:55:51 GMT
X-Mailer: YahooMailClassic/5.4.17 YahooMailWebService/0.7.289.15
Date: Thu, 25 Jun 2009 08:55:51 +0000 (GMT)
From: James Kitambara <jameskitambara@yahoo.co.uk>
Subject: Re: Bucketing Row Data in columns
To: gibsosmat@gmail.com
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-150929727-1245920151=:473"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.095 tagged_above=0 required=5 tests=AWL=1.094,
	HTML_MESSAGE=0.001
X-Spam-Level: *
X-Archive-Number: 200906/101
X-Sequence-Number: 32706


--0-150929727-1245920151=:473
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=A0
Hello Mr. Sandeep Bandela,
=A0
I have gone through your scenario and come up with the following solution.
=A0
SELECT USER_ID, CREATE_TIMESTAMP::DATE, SUM(AMOUNT)
FROM ORDERS
GROUP BY USER_ID, CREATE_TIMESTAMP
ORDER BY USER_ID, CREATE_TIMESTAMP;
=A0
Maybe you need to do little modification on the query to get what you want.
=A0
Best Regards
=A0
James Kitambara
Database Administrator
-------------------------------------ORGINAL MESSAGE-----------------------=
-------------------------
--- On Wed, 24/6/09, Sandeep <gibsosmat@gmail.com> wrote:


From: Sandeep <gibsosmat@gmail.com>
Subject: [SQL] Bucketing Row Data in columns
To: pgsql-sql@postgresql.org
Date: Wednesday, 24 June, 2009, 5:39 PM


Hi all,
I need help on creating a sql, not a problem even if its pl/sql

I have orders table schema is as follow

orders(order_id,user_id, create_timestamp, amount)

and I want to generate a report like
for the past 3 days bucketing purchases i.e SUM(amount) every day in column=
s
i.e result will be having these columns.

(user_id, amount_day1, amount_day2, amount_day3)

ex:
am leaving order_id assume they are auto incrementing and unique, date form=
at dd/mm/yyyy
(user_id, create_timestamp, amount)
(user1, 01/01/2009,100)
(user1, 01/01/2009,100)
(user2, 01/01/2009,100)
(user2, 02/01/2009,100)
(user2, 02/01/2009,100)
(user1, 02/01/2009,100)
(user2, 03/01/2009,100)
(user2, 03/01/2009,100)
(user3, 03/01/2009,100)


result

(user_id, amount_day1, amount_day2, amount_day3)
(user1, 200, 200, 0)
(user2, 100, 200, 200)
(user3, 0, 0, 100)


hope you guys got what I am trying to generate through sql.

I could get this data in each row, but I want it in columns.
Can anyone help me on this? lets assume the buckets are fixed i.e 3 only. b=
ut I wish to get them unlimited i.e day 1 to day 20.





Regards
Sandeep Bandela =0A=0A=0A      
--0-150929727-1245920151=:473
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<table cellspacing=3D"0" cellpadding=3D"0" border=3D"0" ><tr><td valign=3D"=
top" style=3D"font: inherit;"><DIV>&nbsp;</DIV>
<DIV>Hello Mr. Sandeep Bandela,</DIV>
<DIV>&nbsp;</DIV>
<DIV>I have gone through your scenario and come up with the following solut=
ion.</DIV>
<DIV>&nbsp;</DIV>
<DIV>SELECT USER_ID, CREATE_TIMESTAMP::DATE, SUM(AMOUNT)<BR>FROM ORDERS<BR>=
GROUP BY USER_ID, CREATE_TIMESTAMP<BR>ORDER BY USER_ID, CREATE_TIMESTAMP;</=
DIV>
<DIV>&nbsp;</DIV>
<DIV>Maybe you need to do little modification on the query to get what you =
want.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Best Regards</DIV>
<DIV>&nbsp;</DIV>
<DIV>James Kitambara</DIV>
<DIV>Database Administrator</DIV><BR>-------------------------------------O=
RGINAL MESSAGE------------------------------------------------<BR>--- On <B=
>Wed, 24/6/09, Sandeep <I>&lt;gibsosmat@gmail.com&gt;</I></B> wrote:<BR>
<BLOCKQUOTE style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: rgb(=
16,16,255) 2px solid"><BR>From: Sandeep &lt;gibsosmat@gmail.com&gt;<BR>Subj=
ect: [SQL] Bucketing Row Data in columns<BR>To: pgsql-sql@postgresql.org<BR=
>Date: Wednesday, 24 June, 2009, 5:39 PM<BR><BR>
<DIV id=3Dyiv763654426>Hi all,<BR>I need help on creating a sql, not a prob=
lem even if its pl/sql<BR><BR>I have orders table schema is as follow<BR><B=
R>orders(order_id,user_id, create_timestamp, amount)<BR><BR>and I want to g=
enerate a report like<BR>for the past 3 days bucketing purchases i.e SUM(am=
ount) every day in columns<BR>i.e result will be having these columns.<BR><=
BR>(user_id, amount_day1, amount_day2, amount_day3)<BR><BR>ex:<BR>am leavin=
g order_id assume they are auto incrementing and unique, date format dd/mm/=
yyyy<BR>(user_id, create_timestamp, amount)<BR>(user1, 01/01/2009,100)<BR>(=
user1, 01/01/2009,100)<BR>(user2, 01/01/2009,100)<BR>(user2, 02/01/2009,100=
)<BR>(user2, 02/01/2009,100)<BR>(user1, 02/01/2009,100)<BR>(user2, 03/01/20=
09,100)<BR>(user2, 03/01/2009,100)<BR>(user3, 03/01/2009,100)<BR><BR><BR>re=
sult<BR><BR>(user_id, amount_day1, amount_day2, amount_day3)<BR>(user1, 200=
, 200, 0)<BR>(user2, 100, 200, 200)<BR>(user3, 0, 0,
 100)<BR><BR><BR>hope you guys got what I am trying to generate through sql=
.<BR><BR>I could get this data in each row, but I want it in columns.<BR>Ca=
n anyone help me on this? lets assume the buckets are fixed i.e 3 only. but=
 I wish to get them unlimited i.e day 1 to day 20.<BR><BR>
<TABLE cellSpacing=3D0 cellPadding=3D0 width=3D"100%">
<TBODY>
<TR>
<TD>Regards<BR>Sandeep Bandela </TD></TR></TBODY></TABLE></DIV></BLOCKQUOTE=
></td></tr></table><br>=0A=0A=0A=0A      
--0-150929727-1245920151=:473--



From oliveiros.cristina@marktest.pt Thu Jun  25 10:37:34 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 383DB633A0A
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 07:37:34 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 30211-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 07:37:32 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116])
	by mail.postgresql.org (Postfix) with ESMTP id 84C1F6337A9
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 07:37:32 -0300 (ADT)
Received: from suite (suite.marktest.pt [10.61.90.236]) by jupiter.marktest.pt
	with SMTP (Microsoft Exchange Internet Mail Service Version
	5.5.2653.13) id K0H2WAN8; Thu, 25 Jun 2009 11:37:28 +0100
Message-ID: <020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt>
From: "Oliveiros Cristina" <oliveiros.cristina@marktest.pt>
To: <gibsosmat@gmail.com>,
	<pgsql-sql@postgresql.org>
Cc: "Oliveiros Cristina" <oliveiros.cristina@gmail.com>
References: <1245861555.4420.30.camel@devil>
Subject: Re: Bucketing Row Data in columns
Date: Thu, 25 Jun 2009 11:37:29 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0203_01C9F589.51E64560"
X-Priority: 1
X-MSMail-Priority: High
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/102
X-Sequence-Number: 32707

This is a multi-part message in MIME format.

------=_NextPart_000_0203_01C9F589.51E64560
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Hello, Sandeep,

I am not sure if this is what you want.

I came up with this query=20

SELECT *
FROM (SELECT user_id,SUM(amount) as day1 FROM t1 WHERE create_timestamp =
=3D '2009-1-1' GROUP BY "user_id") a
NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE =
create_timestamp =3D '2009-1-2' GROUP BY "user_id") b
NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day3 FROM t1 WHERE =
create_timestamp =3D '2009-1-3' GROUP BY "user_id") c

The solution is not totally correct because it returns NULL in the =
places you return 0.
It seems the SUM() returns NULL when it gets an all NULL column...
Is it problematic for your application ?=20

Also, i 'm not sure if I fully understand your last sentence
lets assume the buckets are fixed i.e 3 only. but I wish to get them =
unlimited i.e day 1 to day 20.

You say that the buckets are fixed at 3. So, you mean the table output =
will always have 4 columns? 3 days plus one for user_id ?
If you want 20 buckets it must be a different query...

Could you please clarify what you mean when you say that you want to get =
a bucket unlimited ?

Best,
Oliveiros
  ----- Original Message -----=20

  From: Sandeep=20
  To: pgsql-sql@postgresql.org=20
  Sent: Wednesday, June 24, 2009 5:39 PM
  Subject: [SQL] Bucketing Row Data in columns


  Hi all,
  I need help on creating a sql, not a problem even if its pl/sql

  I have orders table schema is as follow

  orders(order_id,user_id, create_timestamp, amount)

  and I want to generate a report like
  for the past 3 days bucketing purchases i.e SUM(amount) every day in =
columns
  i.e result will be having these columns.

  (user_id, amount_day1, amount_day2, amount_day3)

  ex:
  am leaving order_id assume they are auto incrementing and unique, date =
format dd/mm/yyyy
  (user_id, create_timestamp, amount)
  (user1, 01/01/2009,100)
  (user1, 01/01/2009,100)
  (user2, 01/01/2009,100)
  (user2, 02/01/2009,100)
  (user2, 02/01/2009,100)
  (user1, 02/01/2009,100)
  (user2, 03/01/2009,100)
  (user2, 03/01/2009,100)
  (user3, 03/01/2009,100)


  result

  (user_id, amount_day1, amount_day2, amount_day3)
  (user1, 200, 200, 0)
  (user2, 100, 200, 200)
  (user3, 0, 0, 100)


  hope you guys got what I am trying to generate through sql.

  I could get this data in each row, but I want it in columns.
  Can anyone help me on this? lets assume the buckets are fixed i.e 3 =
only. but I wish to get them unlimited i.e day 1 to day 20.

        Regards
        Sandeep Bandela =20

------=_NextPart_000_0203_01C9F589.51E64560
Content-Type: text/html;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dutf-8" http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18702">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2 face=3DArial>Hello, Sandeep,</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>I am not sure if this is what you=20
want.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>I came up with this query </FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DCourier>SELECT *<BR>FROM (SELECT =
user_id,SUM(amount) as=20
day1 FROM t1 WHERE create_timestamp =3D '2009-1-1' GROUP BY "user_id")=20
a<BR>NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE =

create_timestamp =3D '2009-1-2' GROUP BY "user_id") b<BR>NATURAL FULL =
JOIN (SELECT=20
user_id,SUM(amount) as day3 FROM t1 WHERE create_timestamp =3D =
'2009-1-3' GROUP BY=20
"user_id") c</FONT></DIV>
<DIV><FONT size=3D2 face=3DCourier></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>The solution is not totally correct =
because it=20
returns NULL in the places you return 0.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>It seems the SUM() returns NULL when it =
gets an all=20
NULL column...</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>Is it problematic for your application =
?=20
</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>Also, i 'm not sure if I fully =
understand your last=20
sentence</FONT></DIV>
<DIV><EM>lets assume the buckets are fixed i.e 3 only. but I wish to get =
them=20
unlimited i.e day 1 to day 20.</EM></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>You say that the buckets are fixed at =
3. So, you=20
mean the table output will always have 4 columns? 3 days plus one for =
user_id=20
?</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>If you want 20 buckets it must be a =
different=20
query...</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>Could you please clarify what you mean =
when you say=20
that you want to get a bucket unlimited ?</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>Best,</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>Oliveiros</FONT></DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px; =
PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV style=3D"FONT: 10pt arial">&nbsp;</DIV>
  <DIV=20
  style=3D"FONT: 10pt arial; BACKGROUND: #e4e4e4; font-color: =
black"><B>From:</B>=20
  <A title=3Dgibsosmat@gmail.com =
href=3D"mailto:gibsosmat@gmail.com">Sandeep</A>=20
  </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3Dpgsql-sql@postgresql.org=20
  href=3D"mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</A> =
</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Wednesday, June 24, 2009 =
5:39=20
  PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> [SQL] Bucketing Row =
Data in=20
  columns</DIV>
  <DIV><BR></DIV>Hi all,<BR>I need help on creating a sql, not a problem =
even if=20
  its pl/sql<BR><BR>I have orders table schema is as=20
  follow<BR><BR>orders(order_id,user_id, create_timestamp, =
amount)<BR><BR>and I=20
  want to generate a report like<BR>for the past 3 days bucketing =
purchases i.e=20
  SUM(amount) every day in columns<BR>i.e result will be having these=20
  columns.<BR><BR>(user_id, amount_day1, amount_day2,=20
  amount_day3)<BR><BR>ex:<BR>am leaving order_id assume they are auto=20
  incrementing and unique, date format dd/mm/yyyy<BR>(user_id, =
create_timestamp,=20
  amount)<BR>(user1, 01/01/2009,100)<BR>(user1, =
01/01/2009,100)<BR>(user2,=20
  01/01/2009,100)<BR>(user2, 02/01/2009,100)<BR>(user2,=20
  02/01/2009,100)<BR>(user1, 02/01/2009,100)<BR>(user2,=20
  03/01/2009,100)<BR>(user2, 03/01/2009,100)<BR>(user3,=20
  03/01/2009,100)<BR><BR><BR>result<BR><BR>(user_id, amount_day1, =
amount_day2,=20
  amount_day3)<BR>(user1, 200, 200, 0)<BR>(user2, 100, 200, =
200)<BR>(user3, 0,=20
  0, 100)<BR><BR><BR>hope you guys got what I am trying to generate =
through=20
  sql.<BR><BR>I could get this data in each row, but I want it in=20
  columns.<BR>Can anyone help me on this? lets assume the buckets are =
fixed i.e=20
  3 only. but I wish to get them unlimited i.e day 1 to day 20.<BR><BR>
  <TABLE cellSpacing=3D0 cellPadding=3D0 width=3D"100%">
    <TBODY>
    <TR>
      <TD>Regards<BR>Sandeep Bandela=20
</TD></TR></TBODY></TABLE></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0203_01C9F589.51E64560--




From robjsargent@gmail.com Thu Jun  25 15:57:47 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 9FCBD63275D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 12:57:47 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 69450-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 12:57:45 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-px0-f189.google.com (mail-px0-f189.google.com
	[209.85.216.189])
	by mail.postgresql.org (Postfix) with ESMTP id 0DBD763227F
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 12:57:45 -0300 (ADT)
Received: by pxi27 with SMTP id 27so387113pxi.15
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 08:57:45 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from
	:user-agent:mime-version:to:subject:references:in-reply-to
	:content-type:content-transfer-encoding;
	bh=b80B1M0ivv265YpFqI0Yj6shm5AcRpkEGdW6FxnbBbQ=;
	b=llWHr0sLnWfZZVV7oRSqrQ88VS2chT8nJwyj/i8H5woK/17mQ54au8DiyDgLefmfvz
	bFTwB2jk5J0loMbcnbOHulsps1PyiXao2sSbDwYZTQVPhYjNDKyA8HtT7SyrJtcXxrlA
	RIU/dncxc2vjsH882oVgYNUWGbpBOo9DiR9fQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=Syn1TK37BNZLRPVnYc4LjDd5sHcspz9NJWM9lj9I1YbHDqeeZCshb5iYbtyPe0JcxK
	et2RI+1eO5SNLmMTXmeA0FX27pfz2Ow39FJFfUSCeRdegm7qkEQiQuyqE4eYws5pNRZG
	c+j1lp+zAxUATNJ/17iytULWeiFvGMrYu6Sx4=
Received: by 10.142.234.16 with SMTP id g16mr912354wfh.97.1245945465247;
	Thu, 25 Jun 2009 08:57:45 -0700 (PDT)
Received: from ?192.168.48.102? ([208.187.158.93])
	by mx.google.com with ESMTPS id 30sm8887743wfa.15.2009.06.25.08.57.44
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Thu, 25 Jun 2009 08:57:44 -0700 (PDT)
Message-ID: <4A439E6E.3050202@gmail.com>
Date: Thu, 25 Jun 2009 09:57:34 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.21 (X11/20090318)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Bucketing Row Data in columns
References: <1245861555.4420.30.camel@devil>
	<020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt>
In-Reply-To: <020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.36 tagged_above=0 required=5 tests=AWL=0.360
X-Spam-Level: 
X-Archive-Number: 200906/103
X-Sequence-Number: 32708

I would be suspicious of this sort of solution of turning rows into 
columns by mean of a series of correlated sub-selects.  Once the data 
set gets large and the number of columns goes over 2 or 3 this will in 
all likelihood not perform well. 
I had the pleasure of re-writing a "report" which was based on count() 
(similar to sum()) per user_id with the counts going into various 
columns per user.  18000 users, a dozen columns from table of 2 million 
rows, report took >1,000,000 seconds (yes almost 12 days) to complete.  
Re-write runs in 5-10 minutes (now at 10M rows) by getting the counts as 
rows (user, item, count) into a temp table and making the columns from 
the temp table (pl/psql)  Getting the counts takes half the time, making 
the flattened report takes half the time.




Oliveiros Cristina wrote:
> Hello, Sandeep,
>  
> I am not sure if this is what you want.
>  
> I came up with this query
>  
> SELECT *
> FROM (SELECT user_id,SUM(amount) as day1 FROM t1 WHERE 
> create_timestamp = '2009-1-1' GROUP BY "user_id") a
> NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE 
> create_timestamp = '2009-1-2' GROUP BY "user_id") b
> NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day3 FROM t1 WHERE 
> create_timestamp = '2009-1-3' GROUP BY "user_id") c
>  
> The solution is not totally correct because it returns NULL in the 
> places you return 0.
> It seems the SUM() returns NULL when it gets an all NULL column...
> Is it problematic for your application ?
>  
> Also, i 'm not sure if I fully understand your last sentence
> /lets assume the buckets are fixed i.e 3 only. but I wish to get them 
> unlimited i.e day 1 to day 20./
>  
> You say that the buckets are fixed at 3. So, you mean the table output 
> will always have 4 columns? 3 days plus one for user_id ?
> If you want 20 buckets it must be a different query...
>  
> Could you please clarify what you mean when you say that you want to 
> get a bucket unlimited ?
>  
> Best,
> Oliveiros
>
>     ----- Original Message -----
>      
>     *From:* Sandeep <mailto:gibsosmat@gmail.com>
>     *To:* pgsql-sql@postgresql.org <mailto:pgsql-sql@postgresql.org>
>     *Sent:* Wednesday, June 24, 2009 5:39 PM
>     *Subject:* [SQL] Bucketing Row Data in columns
>
>     Hi all,
>     I need help on creating a sql, not a problem even if its pl/sql
>
>     I have orders table schema is as follow
>
>     orders(order_id,user_id, create_timestamp, amount)
>
>     and I want to generate a report like
>     for the past 3 days bucketing purchases i.e SUM(amount) every day
>     in columns
>     i.e result will be having these columns.
>
>     (user_id, amount_day1, amount_day2, amount_day3)
>
>     ex:
>     am leaving order_id assume they are auto incrementing and unique,
>     date format dd/mm/yyyy
>     (user_id, create_timestamp, amount)
>     (user1, 01/01/2009,100)
>     (user1, 01/01/2009,100)
>     (user2, 01/01/2009,100)
>     (user2, 02/01/2009,100)
>     (user2, 02/01/2009,100)
>     (user1, 02/01/2009,100)
>     (user2, 03/01/2009,100)
>     (user2, 03/01/2009,100)
>     (user3, 03/01/2009,100)
>
>
>     result
>
>     (user_id, amount_day1, amount_day2, amount_day3)
>     (user1, 200, 200, 0)
>     (user2, 100, 200, 200)
>     (user3, 0, 0, 100)
>
>
>     hope you guys got what I am trying to generate through sql.
>
>     I could get this data in each row, but I want it in columns.
>     Can anyone help me on this? lets assume the buckets are fixed i.e
>     3 only. but I wish to get them unlimited i.e day 1 to day 20.
>
>     Regards
>     Sandeep Bandela
>




From oliveiros.cristina@marktest.pt Thu Jun  25 16:13:47 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id C8571632C7F
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 13:13:47 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 73518-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 13:13:45 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116])
	by mail.postgresql.org (Postfix) with ESMTP id D753963227F
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 13:13:45 -0300 (ADT)
Received: from suite (suite.marktest.pt [10.61.90.236]) by jupiter.marktest.pt
	with SMTP (Microsoft Exchange Internet Mail Service Version
	5.5.2653.13) id K0H2WCNW; Thu, 25 Jun 2009 17:13:42 +0100
Message-ID: <02e701c9f5af$e8cb4f90$ec5a3d0a@marktestcr.marktest.pt>
From: "Oliveiros Cristina" <oliveiros.cristina@marktest.pt>
To: "Rob Sargent" <robjsargent@gmail.com>,
	<pgsql-sql@postgresql.org>
References: <1245861555.4420.30.camel@devil>
	<020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt>
	<4A439E6E.3050202@gmail.com>
Subject: Re: Bucketing Row Data in columns
Date: Thu, 25 Jun 2009 17:13:42 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL=0.000
X-Spam-Level: 
X-Archive-Number: 200906/104
X-Sequence-Number: 32709

I admit that must be a more elegant and faster solution with pl/psql (or 
whatever other languages)

As I don't know nothing about pl/psql I tried with pure sql (if you don't 
have a hunting dog, hunt with a cat)

But obviously this solution doesn't scale well if you have a giant table 
with lots of columns

----- Original Message ----- 
From: "Rob Sargent" <robjsargent@gmail.com>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, June 25, 2009 4:57 PM
Subject: Re: [SQL] Bucketing Row Data in columns


>I would be suspicious of this sort of solution of turning rows into columns 
>by mean of a series of correlated sub-selects.  Once the data set gets 
>large and the number of columns goes over 2 or 3 this will in all 
>likelihood not perform well. I had the pleasure of re-writing a "report" 
>which was based on count() (similar to sum()) per user_id with the counts 
>going into various columns per user.  18000 users, a dozen columns from 
>table of 2 million rows, report took >1,000,000 seconds (yes almost 12 
>days) to complete.  Re-write runs in 5-10 minutes (now at 10M rows) by 
>getting the counts as rows (user, item, count) into a temp table and making 
>the columns from the temp table (pl/psql)  Getting the counts takes half 
>the time, making the flattened report takes half the time.
>
>
>
>
> Oliveiros Cristina wrote:
>> Hello, Sandeep,
>>  I am not sure if this is what you want.
>>  I came up with this query
>>  SELECT *
>> FROM (SELECT user_id,SUM(amount) as day1 FROM t1 WHERE create_timestamp = 
>> '2009-1-1' GROUP BY "user_id") a
>> NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE 
>> create_timestamp = '2009-1-2' GROUP BY "user_id") b
>> NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day3 FROM t1 WHERE 
>> create_timestamp = '2009-1-3' GROUP BY "user_id") c
>>  The solution is not totally correct because it returns NULL in the 
>> places you return 0.
>> It seems the SUM() returns NULL when it gets an all NULL column...
>> Is it problematic for your application ?
>>  Also, i 'm not sure if I fully understand your last sentence
>> /lets assume the buckets are fixed i.e 3 only. but I wish to get them 
>> unlimited i.e day 1 to day 20./
>>  You say that the buckets are fixed at 3. So, you mean the table output 
>> will always have 4 columns? 3 days plus one for user_id ?
>> If you want 20 buckets it must be a different query...
>>  Could you please clarify what you mean when you say that you want to get 
>> a bucket unlimited ?
>>  Best,
>> Oliveiros
>>
>>     ----- Original Message -----
>>      *From:* Sandeep <mailto:gibsosmat@gmail.com>
>>     *To:* pgsql-sql@postgresql.org <mailto:pgsql-sql@postgresql.org>
>>     *Sent:* Wednesday, June 24, 2009 5:39 PM
>>     *Subject:* [SQL] Bucketing Row Data in columns
>>
>>     Hi all,
>>     I need help on creating a sql, not a problem even if its pl/sql
>>
>>     I have orders table schema is as follow
>>
>>     orders(order_id,user_id, create_timestamp, amount)
>>
>>     and I want to generate a report like
>>     for the past 3 days bucketing purchases i.e SUM(amount) every day
>>     in columns
>>     i.e result will be having these columns.
>>
>>     (user_id, amount_day1, amount_day2, amount_day3)
>>
>>     ex:
>>     am leaving order_id assume they are auto incrementing and unique,
>>     date format dd/mm/yyyy
>>     (user_id, create_timestamp, amount)
>>     (user1, 01/01/2009,100)
>>     (user1, 01/01/2009,100)
>>     (user2, 01/01/2009,100)
>>     (user2, 02/01/2009,100)
>>     (user2, 02/01/2009,100)
>>     (user1, 02/01/2009,100)
>>     (user2, 03/01/2009,100)
>>     (user2, 03/01/2009,100)
>>     (user3, 03/01/2009,100)
>>
>>
>>     result
>>
>>     (user_id, amount_day1, amount_day2, amount_day3)
>>     (user1, 200, 200, 0)
>>     (user2, 100, 200, 200)
>>     (user3, 0, 0, 100)
>>
>>
>>     hope you guys got what I am trying to generate through sql.
>>
>>     I could get this data in each row, but I want it in columns.
>>     Can anyone help me on this? lets assume the buckets are fixed i.e
>>     3 only. but I wish to get them unlimited i.e day 1 to day 20.
>>
>>     Regards
>>     Sandeep Bandela
>>
>
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql 




From osvaldo.kussama@gmail.com Thu Jun  25 17:37:54 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 227356327F4
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 14:37:54 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 92112-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 14:37:49 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f226.google.com (mail-bw0-f226.google.com
	[209.85.218.226])
	by mail.postgresql.org (Postfix) with ESMTP id B46F063275D
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 14:37:50 -0300 (ADT)
Received: by bwz26 with SMTP id 26so1742372bwz.19
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 10:37:49 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=XdlVmyS2XlBWZuTgYpfj5h99SBFRvrXCbDiQuTQqwgs=;
	b=BXziOhRZndTjoHh+mFKC01lOAl7hiC4+w2lkJBNg7DN2aGYwJX5P9xOfWkj1VJi2ut
	8Ur6mKbX0HOGtv6xmeBbvEZcR8tkxkT0gpFZIhZOxT949UjCn1mDDuQE6GOrh6ICoWpV
	g/Wd7Qs8mkF7/lUYLcsEQ+PktddfZvzTmUCkA=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=TqCmmiRFQjAa74lZKcLo9wp8h7R8wCJQBJzulQ+/9NbPxvCW6DEnI/FZj6lkjuk4L0
	pIU7F9BOg8JitQiJT+UKfI0kEiLxTXnUCZmrx4vT1tZYrQ8FjCWx7LpJsJp/4wTAXmnA
	stYgtDOQ5utc2fleTu2kJGa20eYy2ucPx9piE=
MIME-Version: 1.0
Received: by 10.204.124.7 with SMTP id s7mr2705991bkr.105.1245951469166; Thu,
	25 Jun 2009 10:37:49 -0700 (PDT)
In-Reply-To: <863824.473.qm@web27906.mail.ukl.yahoo.com>
References: <863824.473.qm@web27906.mail.ukl.yahoo.com>
Date: Thu, 25 Jun 2009 14:37:49 -0300
Message-ID: <690707f60906251037h6d5dd0d5g333a981fef03e38f@mail.gmail.com>
Subject: Re: Bucketing Row Data in columns
From: Osvaldo Kussama <osvaldo.kussama@gmail.com>
To: James Kitambara <jameskitambara@yahoo.co.uk>
Cc: gibsosmat@gmail.com, pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200906/105
X-Sequence-Number: 32710

2009/6/25 James Kitambara <jameskitambara@yahoo.co.uk>:
>
> Hello Mr. Sandeep Bandela,
>
> I have gone through your scenario and come up with the following solution.
>
> SELECT USER_ID, CREATE_TIMESTAMP::DATE, SUM(AMOUNT)
> FROM ORDERS
> GROUP BY USER_ID, CREATE_TIMESTAMP
> ORDER BY USER_ID, CREATE_TIMESTAMP;
>
> Maybe you need to do little modification on the query to get what you want.
>


Contrib tablefunc/crosstab function may help you.
http://www.postgresql.org/docs/current/interactive/tablefunc.html

Osvaldo



From bricklen@gmail.com Thu Jun  25 20:02:33 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id C213B6342AD
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 17:02:33 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 88190-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 25 Jun 2009 17:02:32 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.27])
	by mail.postgresql.org (Postfix) with ESMTP id 9D26063408A
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 17:02:32 -0300 (ADT)
Received: by qw-out-2122.google.com with SMTP id 3so578216qwe.61
	for <pgsql-sql@postgresql.org>; Thu, 25 Jun 2009 13:02:31 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=Nko9pvt2lCtNowBqvnA0yXxEicUWJwoYDI9blJAjUrs=;
	b=vEOzJkYlN/34Te+MUD+aRI+uPFYqDwhCCBqb4ulqCLxFwY7yfQ78FDqxo9V+kgLvOG
	GLjeMptRyoPW+Re81yg/rHARSdSlUHbK5SD330U6RNcHg436fyZ8kwRCXEhlfZBBaCnA
	KT6tEVICJN4H93Zzzek9C5p/laRNnFYBe/DB0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=MsRhswFwNjtHCcLWTV2NqJaG99qxDfVHBw6B5FwxqUfRqYDF1SuiBZHZ0EFEAapmft
	Dh4kKuyFSeAiV28NQmt3cbmjVWFiAHB6JGUwF8gJAbq3uor4jSYd28GTliiq2L9WFXc0
	x7JYiPhUSTAlQFK4GirCQpe+/vD3CAao9ZyLI=
MIME-Version: 1.0
Received: by 10.229.74.212 with SMTP id v20mr958211qcj.16.1245960151520; Thu,
	25 Jun 2009 13:02:31 -0700 (PDT)
In-Reply-To: <1245861555.4420.30.camel@devil>
References: <1245861555.4420.30.camel@devil>
Date: Thu, 25 Jun 2009 13:02:31 -0700
Message-ID: <33b743250906251302m1951715fgbe0928bcabbf8c83@mail.gmail.com>
Subject: Re: Bucketing Row Data in columns
From: bricklen <bricklen@gmail.com>
To: gibsosmat@gmail.com
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200906/106
X-Sequence-Number: 32711

Assuming you know your dates beforehand, you could try a CASE
statement. Something like:
select
  order_id,
  sum(case when timestamp::date = 01/01/2009'' then amount else 0 end)
as amount_day1,
  sum(case when timestamp::date = '02/01/2009' then amount else 0 end)
as amount_day2,
  sum(case when timestamp::date = '03/01/2009' then amount else 0 end)
as amount_day3
from orders
group by order_id

On Wed, Jun 24, 2009 at 9:39 AM, Sandeep<gibsosmat@gmail.com> wrote:
> Hi all,
> I need help on creating a sql, not a problem even if its pl/sql
>
> I have orders table schema is as follow
>
> orders(order_id,user_id, create_timestamp, amount)
>
> and I want to generate a report like
> for the past 3 days bucketing purchases i.e SUM(amount) every day in columns
> i.e result will be having these columns.
>
> (user_id, amount_day1, amount_day2, amount_day3)
>
> ex:
> am leaving order_id assume they are auto incrementing and unique, date
> format dd/mm/yyyy
> (user_id, create_timestamp, amount)
> (user1, 01/01/2009,100)
> (user1, 01/01/2009,100)
> (user2, 01/01/2009,100)
> (user2, 02/01/2009,100)
> (user2, 02/01/2009,100)
> (user1, 02/01/2009,100)
> (user2, 03/01/2009,100)
> (user2, 03/01/2009,100)
> (user3, 03/01/2009,100)
>
>
> result
>
> (user_id, amount_day1, amount_day2, amount_day3)
> (user1, 200, 200, 0)
> (user2, 100, 200, 200)
> (user3, 0, 0, 100)
>
>
> hope you guys got what I am trying to generate through sql.
>
> I could get this data in each row, but I want it in columns.
> Can anyone help me on this? lets assume the buckets are fixed i.e 3 only.
> but I wish to get them unlimited i.e day 1 to day 20.
>
> Regards
> Sandeep Bandela