Post reply

Avg for 5 days

  • May 19, 2005 at 12:02 pm

    #67701

    Hi folks

    Sorry if this question is simple, but I cant get my head around it and have been struggling.

    I have a table something like this:

    Temp¦ Day ¦ Year ¦ Area ¦

    20    ¦ 1     ¦1980 ¦ 5560 ¦

    35    ¦ 2     ¦1980 ¦ 5562 ¦

    22    ¦ 1     ¦ 1981¦ 5560 ¦

    25    ¦ 5     ¦ 1981¦ 5560 ¦

               

    There are 360 days in a year and the data spans over 40 years over 900 seperated area's.

    I need to group the days by 5 days per area /year and get the average tempreture for those 5 days.

    ie. avg temp 22  for day 1 Jan to 5 Jan  in Year 1980 for area 5560

        avg temp 25 ¦ 6Jan -10Jan ¦ 1980 ¦5560

        avg temp 23 ¦ 1Jan-5Jan ¦ 1980¦3520

    etc. etc.

     

    Hope I've explained it properly. Soz folks for the troubles. If anyone could help me with this I would be eternally greatful.

    Thanks in advance.

    Joe

    BTW:- Database running on SQL 2000

     

     

     

  • May 19, 2005 at 12:05 pm

    #559962

    where's the month information??

    Do you keep all the 365 days in the day column?

    Ex feb. 28 = day 59?

  • May 19, 2005 at 12:13 pm

    #559969

    I have another table with days from 1 to 360 and the descriptions next to it ie:

    1 ¦ 1 Jan

    2 ¦ 2 Jan

    3 ¦ 3 Jan

    4 ¦ 4 Jan

    But the table I am working on is exactly like the first table I mentioned in my post, so it has numbers like 4  and 50 and 360 which are numbers of days in a year ( this database runs on the assumption of 360 days in a year instead of 365 - US financial period)

    So to answer your question yep all the day info is in the day coloumn, like 28 feb = day 59 hence no month data.

    Thanks for your help Remi

     

  • May 19, 2005 at 12:29 pm

    #559980

    Quick math from sql server :

    0 / 5 = 0

    1 / 5 = 0

    2 / 5 = 0

    3 / 5 = 0

    4 / 5 = 0

    Select avg(temp) as AvgTemp, (Day - 1)/5 as period, Year, Area

    from dbo.YourTable

    group by Year, Area, (Day - 1)/5

    --Order by Year, Area

    So if you have period 0 it means Jan 01 to jan 05 (which you can redisplay correctly at the front end or rejoin to your days table to get the min and the max day from the calculated period)

  • May 19, 2005 at 12:45 pm

    #559995

    Newb as I am dont understand the (Day-1)/5 statement....

    Anyway have taken your code and advice and am running it right now.

    Will let you know how it turns out, and thanks again for all your help Remi, I've been stuck on this for about 6 hours. 🙂

    Finally can go home now.

  • May 19, 2005 at 12:46 pm

    #559997

    It worked, thank you very much Remi.

  • May 19, 2005 at 1:17 pm

    #560015

    This can clear up the /5 thing :

    Select distinct number, number/5 as num1, (number - 1)/5 as num2 from master.dbo.spt_values where number between 0 and 999 order by number

    As you can see I am creating a new column to group by on the days column. This is the trick.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply