Each Group By expression must contain at least one column that is not an outer reference
WHen running the following query, I get the error message above:
The query, when run without the GroupBy and the two MAX() statements runs properly.
Declare @ProgramID int
Declare @CountyID int
Declare @ReferralID int
Declare @StartDate date
Declare @EndDate date
Declare @WorkerType nvarchar(20)
Set @ProgramID = 11
SET @CountyID = 25
set @ReferralID = 8441
Set @StartDate = '2017-01-01'
Set @EndDate = '2017-01-31'
Set @WorkerType = 'Worker'
SELECT CBI.ProgramID
, CBI.CountyID
, SC.CountyName
, CBI.ReferralID
, R.ReferralName
, Format(@StartDate, 'mm/dd/yyyy') + ' - ' + Format(@EndDate, 'mm/dd/yyyy') as SvcDates
, Max(CBI.Supervisor) as Supervisor
, Max(CBI.TeamLeader) as TeamLeader
FROM dbo.vw_ContactsBasicInfo_PivotedOnStaffType as CBI
LEFT JOIN dbo.tblFPContacts as C ON CBI.ContactID = C.ContactID
LEFT JOIN dbo.tblStateCounties as SC on CBI.CountyID = SC.tblCountyID
LEFT JOIN dbo.tblFPReferrals as R on CBI.ReferralID = R.ReferralID
WHERE (CBI.[Admin] is null)
AND (CBI.ProgramID = @ProgramID)
AND (CBI.CountyID = @CountyID)
AND (CBI.ReferralID = @ReferralID)
AND (C.ContactDate >= @StartDate)
AND (C.ContactDate < DateAdd(day, 1, @EndDate))
Group by CBI.ProgramID
, CBI.CountyID
, SC.CountyName
, CBI.ReferralID
, R.ReferralName
, Format(@StartDate, 'mm/dd/yyyy') + ' - ' + Format(@EndDate, 'mm/dd/yyyy')The query, when run without the GroupBy and the two MAX() statements runs properly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Jeff,
That worked, although I would have expected to receive a message stating that that line could not be in the SELECT clause if not included in an aggregate function or in the group by clause.
That worked, although I would have expected to receive a message stating that that line could not be in the SELECT clause if not included in an aggregate function or in the group by clause.
Hey @Dale, that line contained only "static" data that didn't change from record-to-record, so it didn't need to be aggregated. Does that make sense?
ASKER
Yes, Jeff, that makes sense.
Group by CBI.ProgramID
, CBI.CountyID
/*, SC.CountyName*/
, CBI.ReferralID
/*, R.ReferralName */
, Format(@StartDate, 'mm/dd/yyyy') + ' - ' + Format(@EndDate, 'mm/dd/yyyy')