Union all error - identical columns

• JaybeeSQL

  Hall of Fame

  Points: 3501

  More actions

  July 13, 2016 at 5:17 am

  #327754

  Hi all,

  Trying to merge the result sets of two tables,

  "Column 'dbo.Detail2016.ServerName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

  SELECT

  ServerName,

  DatabaseName,

  LoginName,

  CAST(StartTime AS DATE) [StartTime],

  COUNT(1) [Sessions]

  FROM

  dbo.Detail2016

  WHERE

  ServerName IS NOT NULL

  AND

  LoginName NOT IN ('RHK\SqlMonitor', 'NT AUTHORITY\NETWORK SERVICE')

  AND

  TextData NOT LIKE '-- network protocol:%'

  Union All

  SELECT

  ServerName,

  DatabaseName,

  LoginName,

  CAST(StartTime AS DATE) [StartTime],

  COUNT(1) [Sessions]

  FROM

  dbo.Detail2016From13072016

  WHERE

  ServerName IS NOT NULL

  AND

  LoginName NOT IN ('RHK\SqlMonitor', 'NT AUTHORITY\NETWORK SERVICE')

  AND

  TextData NOT LIKE '-- network protocol:%'

  GROUP BY

  ServerName, DatabaseName, LoginName, CAST(StartTime AS DATE);

• JaybeeSQL

  Hall of Fame

  Points: 3501

  More actions

  July 13, 2016 at 5:24 am

  #1889806

  It's ok I cracked it, just repeated the Group by.

  🙂