Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 383DB633A0A for ; Thu, 25 Jun 2009 07:37:34 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30211-10 for ; Thu, 25 Jun 2009 07:37:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 84C1F6337A9 for ; Thu, 25 Jun 2009 07:37:32 -0300 (ADT) Received: from suite (suite.marktest.pt [10.61.90.236]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id K0H2WAN8; Thu, 25 Jun 2009 11:37:28 +0100 Message-ID: <020601c9f580$f02b7a50$ec5a3d0a@marktestcr.marktest.pt> From: "Oliveiros Cristina" To: , Cc: "Oliveiros Cristina" References: <1245861555.4420.30.camel@devil> Subject: Re: Bucketing Row Data in columns Date: Thu, 25 Jun 2009 11:37:29 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0203_01C9F589.51E64560" X-Priority: 1 X-MSMail-Priority: High X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200906/102 X-Sequence-Number: 32707 This is a multi-part message in MIME format. ------=_NextPart_000_0203_01C9F589.51E64560 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hello, Sandeep, I am not sure if this is what you want. I came up with this query=20 SELECT * FROM (SELECT user_id,SUM(amount) as day1 FROM t1 WHERE create_timestamp = =3D '2009-1-1' GROUP BY "user_id") a NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE = create_timestamp =3D '2009-1-2' GROUP BY "user_id") b NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day3 FROM t1 WHERE = create_timestamp =3D '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 ?=20 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 -----=20 From: Sandeep=20 To: pgsql-sql@postgresql.org=20 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 =20 ------=_NextPart_000_0203_01C9F589.51E64560 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable =EF=BB=BF
Hello, Sandeep,
 
I am not sure if this is what you=20 want.
 
I came up with this query
 
SELECT *
FROM (SELECT = user_id,SUM(amount) as=20 day1 FROM t1 WHERE create_timestamp =3D '2009-1-1' GROUP BY "user_id")=20 a
NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE = create_timestamp =3D '2009-1-2' GROUP BY "user_id") b
NATURAL FULL = JOIN (SELECT=20 user_id,SUM(amount) as day3 FROM t1 WHERE create_timestamp =3D = '2009-1-3' GROUP BY=20 "user_id") c
 
The solution is not totally correct = because it=20 returns NULL in the places you return 0.
It seems the SUM() returns NULL when it = gets an all=20 NULL column...
Is it problematic for your application = ?=20
 
Also, i 'm not sure if I fully = understand your last=20 sentence
lets assume the buckets are fixed i.e 3 only. but I wish to get = them=20 unlimited i.e day 1 to day 20.
 
You say that the buckets are fixed at = 3. So, you=20 mean the table output will always have 4 columns? 3 days plus one for = user_id=20 ?
If you want 20 buckets it must be a = different=20 query...
 
Could you please clarify what you mean = when you say=20 that you want to get a bucket unlimited ?
 
Best,
Oliveiros
----- Original Message -----
 
From:=20 Sandeep=20
Sent: Wednesday, June 24, 2009 = 5:39=20 PM
Subject: [SQL] Bucketing Row = Data in=20 columns

Hi all,
I need help on creating a sql, not a problem = even if=20 its pl/sql

I have orders table schema is as=20 follow

orders(order_id,user_id, create_timestamp, = amount)

and I=20 want to generate a report like
for the past 3 days bucketing = purchases i.e=20 SUM(amount) every day in columns
i.e result will be having these=20 columns.

(user_id, amount_day1, amount_day2,=20 amount_day3)

ex:
am leaving order_id assume they are auto=20 incrementing and unique, date format dd/mm/yyyy
(user_id, = create_timestamp,=20 amount)
(user1, 01/01/2009,100)
(user1, = 01/01/2009,100)
(user2,=20 01/01/2009,100)
(user2, 02/01/2009,100)
(user2,=20 02/01/2009,100)
(user1, 02/01/2009,100)
(user2,=20 03/01/2009,100)
(user2, 03/01/2009,100)
(user3,=20 03/01/2009,100)


result

(user_id, amount_day1, = amount_day2,=20 amount_day3)
(user1, 200, 200, 0)
(user2, 100, 200, = 200)
(user3, 0,=20 0, 100)


hope you guys got what I am trying to generate = through=20 sql.

I could get this data in each row, but I want it in=20 columns.
Can anyone help me on this? lets assume the buckets are = fixed i.e=20 3 only. but I wish to get them unlimited i.e day 1 to day 20.

Regards
Sandeep Bandela=20
------=_NextPart_000_0203_01C9F589.51E64560--