Lists: | pgsql-sql |
---|
From: | James Kitambara <jameskitambara(at)yahoo(dot)co(dot)uk> |
---|---|
To: | gibsosmat(at)gmail(dot)com |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Bucketing Row Data in columns |
Date: | 2009-06-25 08:55:51 |
Message-ID: | 863824.473.qm@web27906.mail.ukl.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Hello Mr. Sandeep Bandela,
I have gone through your scenario and come up with the following solution.
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
-------------------------------------ORGINAL MESSAGE------------------------------------------------
--- On Wed, 24/6/09, Sandeep <gibsosmat(at)gmail(dot)com> wrote:
From: Sandeep <gibsosmat(at)gmail(dot)com>
Subject: [SQL] Bucketing Row Data in columns
To: pgsql-sql(at)postgresql(dot)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 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
From: | Osvaldo Kussama <osvaldo(dot)kussama(at)gmail(dot)com> |
---|---|
To: | James Kitambara <jameskitambara(at)yahoo(dot)co(dot)uk> |
Cc: | gibsosmat(at)gmail(dot)com, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Bucketing Row Data in columns |
Date: | 2009-06-25 17:37:49 |
Message-ID: | 690707f60906251037h6d5dd0d5g333a981fef03e38f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
2009/6/25 James Kitambara <jameskitambara(at)yahoo(dot)co(dot)uk>:
>
> Hello Mr. Sandeep Bandela,
>
> I have gone through your scenario and come up with the following solution.
>
> 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.
>
Contrib tablefunc/crosstab function may help you.
http://www.postgresql.org/docs/current/interactive/tablefunc.html
Osvaldo