Dynamic Averages [message #377126] |
Sat, 20 December 2008 09:53  |
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 #377140 is a reply to message #377126] |
Sat, 20 December 2008 11:59   |
flyboy
Messages: 1903 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   |
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 #377145 is a reply to message #377126] |
Sat, 20 December 2008 12:18   |
flyboy
Messages: 1903 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 #377147 is a reply to message #377146] |
Sat, 20 December 2008 13:05  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|