Re: Bucketing Row Data in columns

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
To: pgsql-sql(at)postgresql(dot)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