Re: GROUP BY DAY.... problem.

"Haximus" <e_at_t.me> wrote in message news:4tpUd.9349$hN1.8282_at_clgrps13...
> <rishib4u_at_gmail.com> wrote in message > news:1109489589.988636.48240_at_g14g2000cwa.googlegroups.com...>> hi everyone,>> i have a query that needs to calculate the AVG of records taken at 15>> minutes interval 24/7 grouped by day for a date range. As per the>> requirements the AVG needs to consider the following guidelines ->> say the the time for which the data has to be avg'd up is 7:00 am to>> 8:00 am then the AVG would be for all data recorded at (7:15, 7:30,>> 7:45 & 8:00) excluding the first record at 7:00 am i.e. > 7:00 AM AND>> <= 8:00 AM.>> NOW the problem is that when i GROUP BY date the AVG function>> calculates the average of all records for that day including the record>> at 0:00 HRS and excluding the record at 24:00 HRS whence it should be>> the exact opposite as per the guidelines.>> Can anyone please suggest a solution?>> thankyou,>> rishi.>> You're going to have to break down the date/time component into something > you can group by, i.e extract the hour and minutes as calculated columns > and GROUP BY hour. If you only want the rage HH:01 to HH+1:00 you're > going to have to get fancy and use the modulus function, or some other > method to derive your hourly grouping factor.