Hourly avg help

[tommo5]

Please help! im new to SQL and im finding a way of calculating an hourly avg difficult.

This is what i have so far for all the data for the week - but i want an hourly avg instead of every second of everyday between. Please help! thanks


select "computerNa me" , "TimeSample d", "PerformanceCou nterName", "SampledVal ue"
from dbo.SDKPerforma nceview
where computername like 'dmm%' and performancecoun tername like 'total messages submitted'
and timesampled between '2007-05-14 00:00:00' and '2007-05-20 23:59:00'

[pradeep kaltari]

Please help! im new to SQL and im finding a way of calculating an hourly avg difficult.

This is what i have so far for all the data for the week - but i want an hourly avg instead of every second of everyday between. Please help! thanks


select "computerNa me" , "TimeSample d", "PerformanceCou nterName", "SampledVal ue"
from dbo.SDKPerforma nceview
where computername like 'dmm%' and performancecoun tername like 'total messages submitted'
and timesampled between '2007-05-14 00:00:00' and '2007-05-20 23:59:00'

Hi,
Try this:
[code=sql]
SELECT AVG(col_name), DATE_FORMAT(tim estamp_column,' %Y/%m/%d %h %p')
FROM <table_name>
WHERE <some condition>
GROUP BY DATE_FORMAT(tim estamp_column,' %H')
[/code]
I hope this helps you.

Regards,
Pradeep.

[tommo5]

I have tried to follow this but im getting loads of errors......

Are you able to help me out by adding my bits into your code? sorry im really a beginner to all this

[pradeep kaltari]

I have tried to follow this but im getting loads of errors......

Are you able to help me out by adding my bits into your code? sorry im really a beginner to all this

Hi,
You said you want hourly average. Could you please be more clear as in which is the column whose average you want to compute.

Regards,
Pradeep.

[tommo5]

sorry - i want the hourly average of the "sampledval ue"

[pradeep kaltari]

sorry - i want the hourly average of the "sampledval ue"

Hi,
Have you tried the following query:

[code=sql]
SELECT AVG(sampledvalu e), DATE_FORMAT(tim esampled,'%Y/%m/%d %h %p')
FROM SDKPerformancev iew
WHERE computername LIKE 'dmm%' AND performancecoun tername LIKE 'total messages submitted' AND timesampled between '2007-05-14 00:00:00' AND '2007-05-20 23:59:00'
GROUP BY DATE_FORMAT(tim esampled,'%H')
[/code]

If you are getting any errors with this then please post them.

Regards,
Pradeep.

[tommo5]

im getting

Server: Msg 195, Level 15, State 10, Line 1
'DATE_FORMAT' is not a recognized function name.

[pradeep kaltari]

Which version of MySQL are you using?

[tommo5]

i believe im using version 8 of the query analyser

[pradeep kaltari]

im getting

Server: Msg 195, Level 15, State 10, Line 1
'DATE_FORMAT' is not a recognized function name.

Hi,
I guess you are using SQL SERVER database. Please confirm.