Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Averages
Dynamic Averages [message #377126] Sat, 20 December 2008 09:53 Go to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Hello;

I need a query to find the average over a period of time----

For example, I have a table with sales by category by date. So a table like:

Date 1 Product 1 Quantity 1
Date 2 Product 1 Quantity 2

and so on...

Now I write a query like this:

[I]Select Avg(Quantity) as Average from Table[/I] 


This only gives me the average at the moment. My question will be how do I get an average that changes with time---I mean I need an extra column that gives me current date along with the average ---so my result will look like---


Date Average
--------------------------
December 1 23.3
December 2 25.5
December 3 20.00

Thanks in advance!
EA
Re: Dynamic Averages [message #377129 is a reply to message #377126] Sat, 20 December 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.

Study AVG in its analytical form.

Regards
Michel
Re: Dynamic Averages [message #377140 is a reply to message #377126] Sat, 20 December 2008 11:59 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
From the given description, I would say that a simple GROUP BY clause on the date column (the same one used in the SELECT clause) shall provide the desired result.

It (GROUP BY clause of the SELECT statement) is deeply described in SQL Reference book. It is available with all Oracle documentation e.g. online on http://tahiti.oracle.com/.
Re: Dynamic Averages [message #377142 is a reply to message #377140] Sat, 20 December 2008 12:04 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Thanks Flyboy. Yes it is true that in this sample case the group by Date clause will solve it. But in actual situation, I have an origin date and a closing date for my entities. I need to find out an average of the number of days it took to close from origin.

So I will need
Select AVG(OriginDate-ClosingDate) From Table;


I doubt I can group this based on Date Criteria.

[Updated on: Sat, 20 December 2008 12:05]

Report message to a moderator

Re: Dynamic Averages [message #377143 is a reply to message #377140] Sat, 20 December 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I interpreted the question as a running average but of course without OP precisions I might be wrong.

Regards
Michel
Re: Dynamic Averages [message #377144 is a reply to message #377143] Sat, 20 December 2008 12:06 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
I think it is Running Average. BTW What is an OP??
Re: Dynamic Averages [message #377145 is a reply to message #377126] Sat, 20 December 2008 12:18 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Select AVG(OriginDate-ClosingDate) From Table;

What is OriginDate? What is ClosingDate? There is no note about it in the first post. Post a testcase as asked by Michel. But maybe he deciphered the requirement correctly, so follow his recommendation - analytic function are also well documented.
Quote:
BTW What is an OP??

Original Poster. What is BTW??
Re: Dynamic Averages [message #377146 is a reply to message #377126] Sat, 20 December 2008 12:31 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
BTW = By the Way Razz

Ok, I will study the Analytical AVG function deeper, and will keep it posted. I will study the link you sent.

Origin Date = Start date for an event
Closing Date = Ending Date for an event.
Re: Dynamic Averages [message #377147 is a reply to message #377146] Sat, 20 December 2008 13:05 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 20 December 2008 17:11
Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel


Previous Topic: SelfJoin?
Next Topic: Package
Goto Forum:
  


Current Time: Fri Dec 09 11:47:07 CST 2016

Total time taken to generate the page: 0.15696 seconds