March 1, 2012 at 8:42 pm
All, I have a date in my table which contains the disk utilization data for each server and for each disk.As i put the sample below. Now i am trying to create a report which shows the AVG disk utilization for each day for each disk.
I am stuck with this as i am trying to use Group by clause but it is giving me the same records for each repeated row.
--- Here is my table looks like---
Name Disk Time Utilization TimeDelta
Test G:\2/20/2012 16:2222.50722565600.922
Test G:\2/20/2012 16:3222.50722565600.172
Test I:\2/20/2012 16:4222.50722565599.859
Test I:\2/20/2012 16:5222.50722565600.047
Test C:\2/20/2012 17:0222.50722565599.985
Test C:\2/20/2012 17:1222.50722565600.093
Test H:\2/20/2012 17:2222.50722565600.688
Test H:\2/20/2012 17:3222.50722565600.14
Test G:\2/20/2012 17:4222.50722565600.25
----here is the query i am trying to use but no luck----
SELECT DISTINCT (DISK),
AVG(Utilization) ,
CONVERT(CHAR(10),PollTime,120)AS Date FROM Disk
WHERE CONVERT(CHAR(10),PollTime,120) = '2012-02-20'
AND name = 'TEST'
Group by Disk,
Utilization ,
ServerName,
PollTime
Order BY Disk desc
Thank For all help.
March 1, 2012 at 9:04 pm
Remove all columns from GROUP BY except DISK and add CONVERT(CHAR(10),PollTime,120)
March 1, 2012 at 9:40 pm
Thanks for your reply.
I tried to just use Disk only in GroupBy clause but it error out to add me other colmun as a part of group by...
Can you give an example that how can i do it..
Thanks
March 1, 2012 at 9:48 pm
something like this?
SELECT DISK
,CONVERT(CHAR(10),PollTime,120)AS Date
,AVG(Utilization)
FROM Disk
WHERE CONVERT(CHAR(10),PollTime,120) = '2012-02-20'
AND name = 'TEST'
Group by Disk, CONVERT(CHAR(10),PollTime,120)
Order BY Disk desc
March 1, 2012 at 9:52 pm
Thanks! I will try it and let you know by the result.... Thanks Again..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply