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

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