From: bricklen <bricklen@gmail.com>
To: gibsosmat@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Bucketing Row Data in columns
Date: Thu, 25 Jun 2009 13:02:31 -0700
Message-ID: <33b743250906251302m1951715fgbe0928bcabbf8c83@mail.gmail.com> (raw)
In-Reply-To: <1245861555.4420.30.camel@devil>
References: <1245861555.4420.30.camel@devil>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bricklen@gmail.com, gibsosmat@gmail.com
Subject: Re: Bucketing Row Data in columns
In-Reply-To: <33b743250906251302m1951715fgbe0928bcabbf8c83@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox