I am trying to write a query to get a simple dataset with weekly average =
numbers.
My table has two columns: Business_Date and Amount.
the dataset that I would like to get should look like: WeekNum, =
Week_Average Where:
Average week1=3D Sum(Amount1 for Week1)/7

Average Week2=3D (Average Week1+ Average Week2)/2

Average Week3=3D (Average Week1+ Average Week2+Average Week3)/3

....

Average Weekn=3D (Average Week1+ Average Week2+...+ Average Weekn)/3



Thank you,

-Halim

"Halim" <hboumedjirek@idealss.com> wrote in message
news:C3CCC10D-C398-43B2-B684-484F2B14492A@news.elevatesoft.com...
I am trying to write a query to get a simple dataset with weekly average
numbers.
My table has two columns: Business_Date and Amount.
the dataset that I would like to get should look like: WeekNum, Week_Average
Where:
Average week1= Sum(Amount1 for Week1)/7

------
Average Week2= (Average Week1+ Average Week2)/2
Are you sure about this? What is "average week 2" here?
------

If what you're looking for is the daily average for each week, try something
like this (it is for a year, but you get the idea)

SELECT
IF(DL_DATE BETWEEN '2001-01-01' AND '2001-12-31' THEN 1 ELSE
IF(DL_DATE BETWEEN '2002-01-01' AND '2002-12-31' THEN 2 ELSE 3)) MYYEAR,
SUM(DL_AMT) MYTOTAL
INTO MEMORY\TEMP
FROM DTABLE
GROUP BY 1;
SELECT MYYEAR, MYTOTAL / 365 DAILYAVERAGE FROM MEMORY\TEMP;

Robert

I was able to get my result using the RUNSUM function.
here is the query:

Select extract(week from "business date") as week1, sum(amount) as total1
into "\memory\mem1"
from cardopHistory
group by week1
order by week1 asc;
Select *, RunSum(total1)/runSum(1)
From "\memory\mem1"
where total1>0
group by Week1


Thanks for your help,
-Halim
"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:35E5F01D-0E1A-47D6-8D53-0B75B468BF8D@news.elevatesoft.com...
>
> "Halim" <hboumedjirek@idealss.com> wrote in message
> news:C3CCC10D-C398-43B2-B684-484F2B14492A@news.elevatesoft.com...
> I am trying to write a query to get a simple dataset with weekly average
> numbers.
> My table has two columns: Business_Date and Amount.
> the dataset that I would like to get should look like: WeekNum,
> Week_Average Where:
> Average week1= Sum(Amount1 for Week1)/7
>
> ------
> Average Week2= (Average Week1+ Average Week2)/2
> Are you sure about this? What is "average week 2" here?
> ------
>
> If what you're looking for is the daily average for each week, try
> something like this (it is for a year, but you get the idea)
>
> SELECT
> IF(DL_DATE BETWEEN '2001-01-01' AND '2001-12-31' THEN 1 ELSE
> IF(DL_DATE BETWEEN '2002-01-01' AND '2002-12-31' THEN 2 ELSE 3)) MYYEAR,
> SUM(DL_AMT) MYTOTAL
> INTO MEMORY\TEMP
> FROM DTABLE
> GROUP BY 1;
> SELECT MYYEAR, MYTOTAL / 365 DAILYAVERAGE FROM MEMORY\TEMP;
>
> Robert
>
>