Home » SQL & PL/SQL » SQL & PL/SQL » accumulator in query (Oracle 10g)
accumulator in query [message #572557] Thu, 13 December 2012 03:57 Go to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi,
I need to get an accumulated value for a count.
E.g. The table has purchased date, purchased item, purchased item type. The count of purchased item groyup by purchased type on every purchased date. Now, we got the count value (purchased item). But, I want the accumulated count value on every purchased date. So that I will get that how many items has been purchased on a particular date.

Thanx in advance.
Regds,
Lenin.
Re: accumulator in query [message #572564 is a reply to message #572557] Thu, 13 December 2012 04:36 Go to previous messageGo to next message
Littlefoot
Messages: 19890
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
To me, it sounds as if analytic functions might help here. Would you mind posting both test case and desired result?
Re: accumulator in query [message #572568 is a reply to message #572564] Thu, 13 December 2012 04:53 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Thank you Littlefoot for immediate response.
Fortunately I got the result with the help of my colleague.
Here is the query:
------------------
select p_type,p_date,max(total_item)
from(
SELECT p_type,
trunc(p_date) p_date,
count(p_item) OVER(PARTITION BY p_type ORDER BY trunc(p_date) RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW) AS Total_item
FROM p_items a
where
1=1
and p_date is not null
)
group by p_type,p_date
order by p_type,p_date;

Result:
-------
ASU 10/04/2012 9
ASU 30/11/2012 10
CLT 02/12/2001 2
CLT 10/01/2011 3
EST 07/10/1991 1

Thank you once again. Catch you in my next critical moment.
Regds,
Lenin.
Re: accumulator in query [message #572569 is a reply to message #572564] Thu, 13 December 2012 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or maybe a ROLLUP or CUBE on GROUP BY.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: accumulator in query [message #572572 is a reply to message #572569] Thu, 13 December 2012 05:41 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Sure Michel. From next time, I will follow you guide.
Regds,
Lenin.
Re: accumulator in query [message #572574 is a reply to message #572572] Thu, 13 December 2012 05:57 Go to previous message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But now you can't have any help without posting a test case, and so the guide.

Regards
Michel
Previous Topic: nullifying variables
Next Topic: date to char ,char to date
Goto Forum:
  


Current Time: Sat Dec 20 18:53:12 CST 2014

Total time taken to generate the page: 0.62161 seconds