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 "computerName" , "TimeSampled", "PerformanceCounterName", "SampledValue"
from dbo.SDKPerformanceview
where computername like 'dmm%' and performancecountername 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 "computerName" , "TimeSampled", "PerformanceCounterName", "SampledValue"
from dbo.SDKPerformanceview
where computername like 'dmm%' and performancecountername like 'total messages submitted'
and timesampled between '2007-05-14 00:00:00' and '2007-05-20 23:59:00'

Hi,
Try this:

SELECT AVG(col_name), DATE_FORMAT(timestamp_column,'%Y/%m/%d %h %p')
FROM <table_name>
WHERE <some condition>
GROUP BY DATE_FORMAT(timestamp_column,'%H')
 

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 "sampledvalue"

[pradeep kaltari]

sorry - i want the hourly average of the "sampledvalue"

Hi,
Have you tried the following query:

SELECT AVG(sampledvalue), DATE_FORMAT(timesampled,'%Y/%m/%d %h %p')
FROM SDKPerformanceview
WHERE computername LIKE 'dmm%' AND performancecountername LIKE 'total messages submitted'  AND timesampled between '2007-05-14 00:00:00' AND '2007-05-20 23:59:00'
GROUP BY DATE_FORMAT(timesampled,'%H')
 

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.