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 |
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2013-08-05 : 16:50:36
|
The below script I wrote is currently grouping by all the columns. But I need help getting the script to group by the Month column, and add up the Run Count for that particular Month.Select es.GeographicLocationDescription ,es.BottomUp01ID As CurrentDirectSupID ,es.BottomUp01Name As CurrentDirectSupName ,es.EmployeeID ,es.EmployeeName ,COUNT(lg.NetworkID) As "Run Count" ,AVG(lg.RunTime) As "Avg Response Time" ,me.DescriptionName ,DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(lg.ExecutionStartDateTime) AS VARCHAR(4)) as MonthFROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog lgInner Join dbEmployee.Summary.tblEmployeeSnapshot es On lg.NetworkID = es.NetworkIDInner Join dbMyInfo.config.tblMeasurementDropDown me On lg.MetricID = me.ID WHERE es.StatusID = 'A'and lg.Error IS NULL GROUP BY lg.ExecutionStartDateTime ,es.GeographicLocationDescription ,es.BottomUp01ID ,es.BottomUp01Name ,es.EmployeeID ,es.EmployeeName ,me.DescriptionName Order By lg.ExecutionStartDateTime ,es.GeographicLocationDescription ,es.BottomUp01ID ,es.BottomUp01Name ,es.EmployeeID ,es.EmployeeName ,me.DescriptionName |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-05 : 17:54:16
|
Do one of two things:1. Remove all the columns that you don't want in the group by list from the group by list AND the select list - for example like shown belowSelect COUNT(lg.NetworkID) As "Run Count" ,AVG(lg.RunTime) As "Avg Response Time" ,DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(lg.ExecutionStartDateTime) AS VARCHAR(4)) as MonthFROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog lgInner Join dbEmployee.Summary.tblEmployeeSnapshot es On lg.NetworkID = es.NetworkIDInner Join dbMyInfo.config.tblMeasurementDropDown me On lg.MetricID = me.IDWHERE es.StatusID = 'A'and lg.Error IS NULL GROUP BY DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(lg.ExecutionStartDateTime) AS VARCHAR(4)) 2. Wrap any of the columns you want to keep in an aggregate function (such as MAX, MIN etc.). This may not necessarily give you the values from one row out of many, and so it may not be what you wantSelect MAX(es.GeographicLocationDescription) AS GeographicLocationDescription ,MAX(es.BottomUp01ID) As CurrentDirectSupID ,COUNT(lg.NetworkID) As "Run Count" ,AVG(lg.RunTime) As "Avg Response Time" ,DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(lg.ExecutionStartDateTime) AS VARCHAR(4)) as MonthFROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog lgInner Join dbEmployee.Summary.tblEmployeeSnapshot es On lg.NetworkID = es.NetworkIDInner Join dbMyInfo.config.tblMeasurementDropDown me On lg.MetricID = me.IDWHERE es.StatusID = 'A'and lg.Error IS NULL GROUP BY DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(lg.ExecutionStartDateTime) AS VARCHAR(4))Order By es.GeographicLocationDescription ,CurrentDirectSupID |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-06 : 00:02:24
|
Either of before suggestions or this?Select es.GeographicLocationDescription ,es.BottomUp01ID As CurrentDirectSupID ,es.BottomUp01Name As CurrentDirectSupName ,es.EmployeeID ,es.EmployeeName ,COUNT(lg.NetworkID) OVER (PARTITION BY DATEDIFF(mm,0,lg.ExecutionStartDateTime)) As "Run Count" ,AVG(lg.RunTime) OVER (PARTITION BY DATEDIFF(mm,0,lg.ExecutionStartDateTime)) As "Avg Response Time" ,me.DescriptionName ,DATENAME(MM, lg.ExecutionStartDateTime) + ' ' + DATENAME(yyyy,lg.ExecutionStartDateTime) as MonthFROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog lgInner Join dbEmployee.Summary.tblEmployeeSnapshot es On lg.NetworkID = es.NetworkIDInner Join dbMyInfo.config.tblMeasurementDropDown me On lg.MetricID = me.ID WHERE es.StatusID = 'A'and lg.Error IS NULL Order By lg.ExecutionStartDateTime ,es.GeographicLocationDescription ,es.BottomUp01ID ,es.BottomUp01Name ,es.EmployeeID ,es.EmployeeName ,me.DescriptionName depending on whether you want details along with aggregated fields or not------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2013-08-06 : 16:47:57
|
Thanks guys! I used a temp table to get it working last night, but will definitely try these. |
|
|
sivadss2007
Starting Member
18 Posts |
Posted - 2013-08-28 : 10:31:07
|
You can take the whole output into a temporary table and then apply group by on temp tableor elseSelect GeographicLocationDescription ,CurrentDirectSupID ,CurrentDirectSupName ,EmployeeID ,EmployeeName ,[Run Count] ,[Avg Response Time] ,DescriptionName ,[Month]from(Select es.GeographicLocationDescription ,es.BottomUp01ID As CurrentDirectSupID ,es.BottomUp01Name As CurrentDirectSupName ,es.EmployeeID ,es.EmployeeName ,COUNT(lg.NetworkID) As "Run Count" ,AVG(lg.RunTime) As "Avg Response Time" ,me.DescriptionName ,DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(lg.ExecutionStartDateTime) AS VARCHAR(4)) as MonthFROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog lgInner Join dbEmployee.Summary.tblEmployeeSnapshot es On lg.NetworkID = es.NetworkIDInner Join dbMyInfo.config.tblMeasurementDropDown me On lg.MetricID = me.ID WHERE es.StatusID = 'A'and lg.Error IS NULL GROUP BY lg.ExecutionStartDateTime ,es.GeographicLocationDescription ,es.BottomUp01ID ,es.BottomUp01Name ,es.EmployeeID ,es.EmployeeName ,me.DescriptionName Order By lg.ExecutionStartDateTime ,es.GeographicLocationDescription ,es.BottomUp01ID ,es.BottomUp01Name ,es.EmployeeID ,es.EmployeeName ,me.DescriptionName ) group by Run Count,monthP.Siva |
|
|
|
|
|
|
|