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.

Okay... now scratch out those other two brainfarts! ;)

You need FLOOR(absolute minutes / interval) for a group by

There... that's better Received on Sun Feb 27 2005 - 14:16:59 CST