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<gibsos...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
