Average items per month

[acewilli]

Hello,

I am needing to take the average of a column for the dates that I specify and return the number. See example below.

filelength is data type int and is in seconds in the database.
master_index is the table in the database.
filedatetime is data type (I didn't do this) nvarchar in the database and looks like this "2006-06-01 00:00:00.000" (I did not do this either).

The Select Statement is below or what I have so far.

select cast(sum(filelength) as float) / 60 as ColAvg from master_index where filedatetime between '"&StartDate&" 00:00:00.000' and '"&EndDate&" 23:00:00.000'"

I want to know on average the filelength over a specified date range that I specify with "StartDate" and "EndDate". I already have the code for doing that and outputing the results but I need to know how to get the calculations correct so that I output the one number that is the average.

I hope this makes sense and please feel free to ask me if it doesn't.

Any help is greatly appreciated and thanks in advance.

Thank you,

Ace

 

[gmmastros]

You want an average, but you're using the sum function. Have you tried the avg function?

select cast([!]avg[/!](filelength) as float) / 60 as ColAvg from master_index where filedatetime between '"&StartDate&" 00:00:00.000' and '"&EndDate&" 23:00:00.000'"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

My apologies for not answering your question. I see that you want an average grouped by month. Try this...

select cast(avg(filelength) as float) / 60 as ColAvg,
       Month(Convert(DateTime, FileDateTime)) As MonthNumber
from   master_index 
where  Convert(DateTime, filedatetime) between '"&StartDate&"' and '"&EndDate&" 23:00:00.000'" 
Group By Month(Convert(DateTime, FileDateTime))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[acewilli]

This works perfectly except this is giving me the average per record. Such as 375 Total Records and 1715 Minutes = 4.753333 where I need it to give me the average per day for the whole month....this is hard to explain. I need to know on average how long (filelength) the records are over a given date range but I need to know the average per day for the date range that I give. Does that make any sense?

So, I would give it the date range I want it to average but then I want it to tell me the average the filelength is per day over the date range I specify.

Again...thank you so much for your help. The code worked like a charm but wasn't quite what I needed as far as calculating it. If it is a simple change please let me know.

Thanks again,

Ace

 

[acewilli]

For whatever reason this is working almost perfectly. It isn't right on to the decimal point but it is very close. Not sure why it works though??

select cast(avg(filelength) as float) / 9.55 as ColAvg, Month(Convert(DateTime, FileDateTime)) As MonthNumber from master_index where Convert(DateTime, filedatetime) between '"&strbdate&" 00:00:00.000' and '"&stredate&" 23:00:00.000' group by Month(Convert(DateTime, FileDateTime))

Let me know if you know why this is working but it is giving me the correct average hours of all the days in each month.

Thanks in advance,

Ace

 

[MaffewW]

try casting as decimal rather than float

Matt

Brighton, UK