Averaging Data - Don't want it

[adamr99]

I have a tool that is automatically gennerating some SQL in access to output to a graphing report. The data is performance counters collected every 5 min however, the SQL that is gennerated (shown below) averages the values for every hour. I see the AVG keyword, but whenever I try to get rid of it, it give me an error. Any help would be apprciated.

Thanks
AdamR

set transaction isolation level read uncommitted set deadlock_priority low SELECT [time]=CONVERT(smalldatetime,CONVERT(char(4),DATEPART( yyyy,DATEADD(mi,-300,time)))+'/'+CONVERT(char(2),DATEPART( mm,DATEADD(mi,-300,time)))+'/'+CONVERT(char(2),DATEPART( dd,DATEADD(mi,-300,time)))+' '+CONVERT(char(2),DATEPART( hh,DATEADD(mi,-300,time)))+':01',101), server, [Memory-Available Bytes (Scale = MB)]=AVG([Memory-Available Bytes (Scale = MB)]), [Memory-Committed Bytes (Scale = MB)]=AVG([Memory-Committed Bytes (Scale = MB)]), [Memory-Pages/sec (Scale = 1)]=AVG([Memory-Pages/sec (Scale = 1)]), [Memory-Page Reads/sec (Scale = 1)]=AVG([Memory-Page Reads/sec (Scale = 1)]), [Memory-Page Writes/sec (Scale = 1)]=AVG([Memory-Page Writes/sec (Scale = 1)]), [Process-Page File Bytes-_Total (Scale = MB)]=AVG([Process-Page File Bytes-_Total (Scale = MB)]) FROM MemoryPerformanceView WHERE ([time] BETWEEN CONVERT(datetime, '20020609 5:0:0') AND CONVERT(datetime, '20020616 5:0:0') ) GROUP BY CONVERT(smalldatetime,CONVERT(char(4),DATEPART( yyyy,DATEADD(mi,-300,time)))+'/'+CONVERT(char(2),DATEPART( mm,DATEADD(mi,-300,time)))+'/'+CONVERT(char(2),DATEPART( dd,DATEADD(mi,-300,time)))+' '+CONVERT(char(2),DATEPART( hh,DATEADD(mi,-300,time)))+':01',101), server ORDER BY CONVERT(smalldatetime,CONVERT(char(4),DATEPART( yyyy,DATEADD(mi,-300,time)))+'/'+CONVERT(char(2),DATEPART( mm,DATEADD(mi,-300,time)))+'/'+CONVERT(char(2),DATEPART( dd,DATEADD(mi,-300,time)))+' '+CONVERT(char(2),DATEPART( hh,DATEADD(mi,-300,time)))+':01',101)

 

[SQLBill]

You can't just get rid of the AVG...take this as an example:

[Memory-Page Reads/sec (Scale = 1)]=AVG([Memory-Page Reads/sec (Scale = 1)]),

If you just take out the AVG...the script won't work because it's trying to put more than one item into the variable.

ie: variable = AVG(1, 2, 3, 4)

What you need to do it either take out the whole portion of the statement, or change the AVG to what best suits your requirement.

For example:

SUM....will add all the given information
MAX....will provide the largest information.

so...

variable = AVG(1,2,3,4) makes variable = 5
variable = SUM(1,2,3,4) makes variable = 10
variable = MAX(1,2,3,4) makes variable = 4

Hope this helped a bit...

-SQLBill