Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B25A763575B for ; Thu, 25 Jun 2009 05:56:01 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 59547-01-7 for ; Thu, 25 Jun 2009 05:55:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from web27906.mail.ukl.yahoo.com (web27906.mail.ukl.yahoo.com [217.146.182.56]) by mail.postgresql.org (Postfix) with SMTP id AE4956351EE for ; Thu, 25 Jun 2009 05:55:54 -0300 (ADT) Received: (qmail 1850 invoked by uid 60001); 25 Jun 2009 08:55:51 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1245920151; bh=RYqni+7Fp7W8MUR5jTI9ur2+eB2A4IrK/jLalR7rql8=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=iVeQ/thaUaXCeLjMCeX02JevH6aTmsWLq/zms6iYY57+okXGL/tI03VH/COSwTSu991kN9PK7nWbLtXEVoN/C2qtXwPR9QBKpJ8qUQneG0hf93HBi0RCU3ipI+h8l3maOxkxE2mBPZ65BoPj1sNQhUfUvkIUZK9+AlZ921XAUDk= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=Gr60LAKStWPJAUFPoy0v0ZZjQhc48Y8Uj4K36dzzgEep1ARCKhoLzou6xchQQf7EGxXTUYsAo8a2+3JoxbOnmjKX81ml7vi+jGrRZGEepZjTnEOFc5LmCgiTAQmiXwkGVK8cCp51w7r1BTPhstTXTYQSlNx5QYeV6NyCSa878Dc=; Message-ID: <863824.473.qm@web27906.mail.ukl.yahoo.com> X-YMail-OSG: O8G.zAMVM1m6Lgn1Ct0DYjpQqUpI2XnxW3YY52pMcI3JJVqzT.bdMg_yFHknVPHPk7WAYs237IPqlxRTm00KRs_ELkx8CT_b5QW70fyIrbSNBzQHidL6jKxhVuoBtwGieOxhkK034TKeP0cN6BkL0jql6GWyfcX4MQMhNbvttm8b1.tQ2D_xEUFIReTe1BPVfJX6KM5tTwj6KrKDfzPkPfJXkLL3U8SADbkWWu__jdvAQZD8zR4eTbOi43Hb.5gpP1iEJeHlqFxXCOxxaIWIoO.TxG0_csZNu8VUVtY5jdhtTlSBC06Q9EVzfcpCYtBk51U- Received: from [196.43.80.43] by web27906.mail.ukl.yahoo.com via HTTP; Thu, 25 Jun 2009 08:55:51 GMT X-Mailer: YahooMailClassic/5.4.17 YahooMailWebService/0.7.289.15 Date: Thu, 25 Jun 2009 08:55:51 +0000 (GMT) From: James Kitambara Subject: Re: Bucketing Row Data in columns To: gibsosmat@gmail.com Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-150929727-1245920151=:473" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.095 tagged_above=0 required=5 tests=AWL=1.094, HTML_MESSAGE=0.001 X-Spam-Level: * X-Archive-Number: 200906/101 X-Sequence-Number: 32706 --0-150929727-1245920151=:473 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable =A0 Hello Mr. Sandeep Bandela, =A0 I have gone through your scenario and come up with the following solution. =A0 SELECT USER_ID, CREATE_TIMESTAMP::DATE, SUM(AMOUNT) FROM ORDERS GROUP BY USER_ID, CREATE_TIMESTAMP ORDER BY USER_ID, CREATE_TIMESTAMP; =A0 Maybe you need to do little modification on the query to get what you want. =A0 Best Regards =A0 James Kitambara Database Administrator -------------------------------------ORGINAL MESSAGE-----------------------= ------------------------- --- On Wed, 24/6/09, Sandeep wrote: From: Sandeep Subject: [SQL] Bucketing Row Data in columns To: pgsql-sql@postgresql.org Date: Wednesday, 24 June, 2009, 5:39 PM 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 column= s 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 form= at 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. b= ut I wish to get them unlimited i.e day 1 to day 20. Regards Sandeep Bandela =0A=0A=0A --0-150929727-1245920151=:473 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
 
Hello Mr. Sandeep Bandela,
 
I have gone through your scenario and come up with the following solut= ion.
 
SELECT USER_ID, CREATE_TIMESTAMP::DATE, SUM(AMOUNT)
FROM ORDERS
= GROUP BY USER_ID, CREATE_TIMESTAMP
ORDER BY USER_ID, CREATE_TIMESTAMP;
 
Maybe you need to do little modification on the query to get what you = want.
 
Best Regards
 
James Kitambara
Database Administrator

-------------------------------------O= RGINAL MESSAGE------------------------------------------------
--- On Wed, 24/6/09, Sandeep <gibsosmat@gmail.com> wrote:

From: Sandeep <gibsosmat@gmail.com>
Subj= ect: [SQL] Bucketing Row Data in columns
To: pgsql-sql@postgresql.orgDate: Wednesday, 24 June, 2009, 5:39 PM

Hi all,
I need help on creating a sql, not a prob= lem 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 g= enerate a report like
for the past 3 days bucketing purchases i.e SUM(am= ount) every day in columns
i.e result will be having these columns.
<= BR>(user_id, amount_day1, amount_day2, amount_day3)

ex:
am leavin= g 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/20= 09,100)
(user2, 03/01/2009,100)
(user3, 03/01/2009,100)


re= sult

(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.
Ca= n 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

=0A=0A=0A=0A --0-150929727-1245920151=:473--