Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Weekly Averages |
Fri, Jan 2 2009 3:31 PM | Permanent Link |
"Halim" | 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 |
Fri, Jan 2 2009 4:46 PM | Permanent Link |
"Robert" | "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 |
Fri, Jan 2 2009 7:10 PM | Permanent Link |
"Halim" | 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 > > |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |