Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
accessdbguru
Starting Member
26 Posts |
 Posted - 2012-02-02 : 11:04:41
|
| Folks, I have this SQL statement and it is not pulling the average value correctly. Goal: I need to get average of all percentage values for the last 30 days from current date [Column:TimeStamp]. Timestamp format is DateTime like 2011-11-12 00:00:00.000 and so on... Problem: My end result is not grouping at all. I see multiple rows displayed. What I am doing wrong? MY SQL STATEMENT:SELECT dbo.APM_ApplicationStatus_Daily.ApplicationID, AVG(dbo.APM_ApplicationStatus_Daily.PercentAvailability) AS AVERAGE, dbo.APM_Application.Name AS Application_Name FROM dbo.APM_ApplicationStatus_Daily INNER JOIN dbo.APM_Application ON dbo.APM_ApplicationStatus_Daily.ApplicationID = dbo.APM_Application.IDWHERE (dbo.APM_ApplicationStatus_Daily.Timestamp >= DATEADD(day, DATEDIFF(day, 30, GETDATE()) - 30, 0)) AND (dbo.APM_ApplicationStatus_Daily.Timestamp < DATEADD(day, DATEDIFF(day, 30, GETDATE()) + 1, 0))GROUP BY ApplicationID, dbo.APM_Application.Name,dbo.APM_ApplicationStatus_Daily.PercentAvailability ORDER BY ApplicationID ASC |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-02 : 11:24:04
|
Take a look at your result and then at the grouped columns.We can't see so please tell us where is the difference that prevents the grouping.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-02 : 11:45:10
|
quote:
Originally posted by accessdbguru
Folks, I have this SQL statement and it is not pulling the average value correctly. Goal: I need to get average of all percentage values for the last 30 days from current date [Column:TimeStamp]. Timestamp format is DateTime like 2011-11-12 00:00:00.000 and so on... Problem: My end result is not grouping at all. I see multiple rows displayed. What I am doing wrong? MY SQL STATEMENT:SELECT dbo.APM_ApplicationStatus_Daily.ApplicationID, AVG(dbo.APM_ApplicationStatus_Daily.PercentAvailability) AS AVERAGE, dbo.APM_Application.Name AS Application_Name FROM dbo.APM_ApplicationStatus_Daily INNER JOIN dbo.APM_Application ON dbo.APM_ApplicationStatus_Daily.ApplicationID = dbo.APM_Application.IDWHERE (dbo.APM_ApplicationStatus_Daily.Timestamp >= DATEADD(day, DATEDIFF(day, 30, GETDATE()) - 30, 0)) AND (dbo.APM_ApplicationStatus_Daily.Timestamp < DATEADD(day, DATEDIFF(day, 30, GETDATE()) + 1, 0))GROUP BY ApplicationID, dbo.APM_Application.Name,dbo.APM_ApplicationStatus_Daily.PercentAvailability ORDER BY ApplicationID ASC
currently you're grouping by Applicatin fields. so you'll get average for each applicationi didnt understand why you need percentage in GROUP BY though ( I feel its not to be included)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
accessdbguru
Starting Member
26 Posts |
Posted - 2012-02-02 : 12:05:05
|
| Yes that works! THANK YOU. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-02 : 12:09:15
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Aggregate SQL