From: Oliveiros Cristina <oliveiros.cristina@marktest.pt>
To: gibsosmat@gmail.com
To: pgsql-sql@postgresql.org
Cc: Oliveiros Cristina <oliveiros.cristina@gmail.com>
Subject: Re: Bucketing Row Data in columns
Date: Thu, 25 Jun 2009 11:37:29 +0100
Message-ID: <020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt> (raw)
References: <1245861555.4420.30.camel@devil>
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@postgresql.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
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: oliveiros.cristina@marktest.pt, gibsosmat@gmail.com, oliveiros.cristina@gmail.com
Subject: Re: Bucketing Row Data in columns
In-Reply-To: <020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt>
* 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