How to group by a column - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to group by a column

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 Month



FROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog  lg

Inner Join dbEmployee.Summary.tblEmployeeSnapshot     es
        On lg.NetworkID = es.NetworkID

Inner 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 below

Select 
      COUNT(lg.NetworkID) As "Run Count"
      ,AVG(lg.RunTime)     As "Avg Response Time" 
      ,DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(lg.ExecutionStartDateTime) AS VARCHAR(4)) as Month
FROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog  lg
Inner Join dbEmployee.Summary.tblEmployeeSnapshot     es
        On lg.NetworkID = es.NetworkID
Inner Join dbMyInfo.config.tblMeasurementDropDown     me 
        On lg.MetricID = me.ID
WHERE 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 want

Select 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 Month

FROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog  lg
Inner Join dbEmployee.Summary.tblEmployeeSnapshot     es
        On lg.NetworkID = es.NetworkID

Inner Join dbMyInfo.config.tblMeasurementDropDown     me 
        On lg.MetricID = me.ID
WHERE 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 Month



FROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog  lg

Inner Join dbEmployee.Summary.tblEmployeeSnapshot     es
        On lg.NetworkID = es.NetworkID

Inner 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 MVP
http://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 table

or else
Select 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 Month

FROM dbMyInfo.Detail.tblCallDetailTotalsExecutionLog lg

Inner Join dbEmployee.Summary.tblEmployeeSnapshot es
On lg.NetworkID = es.NetworkID

Inner 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,month

P.Siva

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources