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