Receiving 'Aggregate cannot appear in an ON clause' Error

0
votes

I'm receiving this error: An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

I'm unaware of how to fix this, please help.

set datefirst 2

select [Table 2].[Active_Headcount]
    , DATEADD(DD, 7 - (DATEPART(DW, MIN([Table 1].[Term Date]))), MIN([Table 1].[Term Date])) as EndOfWeek
    , COUNT(*) as TermsPerWeek
from [Table 1]
left join [Table 2]
    on (DATEADD(DD, 7 - (DATEPART(DW, MIN([Table 1].[Term Date]))), MIN([Table 1].[Term Date]))) = [Table 2].[WeekDate]
where [Table 1].[Term Date] not like 'null'
    and (
        [Table 1].[Term Date] like '%2016%'
        or [Table 1].[Term Date] like '%2017%'
        )
group by DATEPART(WEEK, [Table 1].[Term Date])
    , [Table 2].[Active_Headcount]
order by EndOfWeek asc;
2
sqlsql-server-2012
(1) Tag your question with the database you are using. (2) Describe what you want to do. Syntactically incorrect queries do not always convey the intention of the person writing them. - Gordon Linoff
I'm aggregating dates [Term Date] 'as EndOfWeek', to end on Monday and want to join Table 2 (date also ends on Monday). The issue I'm running into, is that, I need to join based on 'EndOfWeek' and not [Term Date]. - J. Hayes
Please edit to add some sample data and expected results. - Damien_The_Unbeliever
Would you please edit your query to indicate which columns come from which table? (For example, change Active_Headcount to [Table 1].Active_Headcount.) Thanks! - Ben Gribaudo
@BenGribaudo I made the edits - J. Hayes

2 Answers

0
votes

Basically it is what it says. You can't do aggregate operations in the ON clause of a JOIN condition. You need to use APPLY operator.

Also, I'll assume from your code that [HeadcountByWeek] is your "table 2":

set datefirst 2

select Active_Headcount
    , DATEADD(DD, 7 - (DATEPART(DW, MIN([Term Date]))), MIN([Term Date])) as EndOfWeek
    , COUNT(*) as TermsPerWeek
from table t1
outer apply (
    select * 
    from [HeadcountByWeek] t2 
    where (DATEADD(DD, 7 - (DATEPART(DW, MIN(t1.[Term Date]))), MIN(t1.[Term Date]))) = t2.[WeekDate] ) t2
where t1.[Term Date] not like 'null'
    and (
        t1.[Term Date] like '%2016%'
        or t1.[Term Date] like '%2017%'
        )
group by DATEPART(WEEK, t1.[Term Date])
    , t1.[Active_Headcount]
order by EndOfWeek asc;
0
votes

I would do something like this:

set datefirst 2;

With cte(Active_Headcount, EndOfWeek, TermsPerWeek)
as (select Active_Headcount
, DATEADD(DD, 7 - (DATEPART(DW, MIN([Term Date]))), MIN([Term Date])) as EndOfWeek
, COUNT(*) as TermsPerWeek
from table 1
)
select * from cte
left join table 2
on cte.EndOfWeek = [HeadcountByWeek].[WeekDate]
where [Term Date] not like 'null'
and (
    [Term Date] like '%2016%'
    or [Term Date] like '%2017%'
    )
 group by DATEPART(WEEK, [Term Date])
, [Active_Headcount]
order by EndOfWeek asc;

This won't work out of the box, because I don't know which columns come from which table.

The main idea is to break the query into a CTE and then use the column from that cte in your joins below.