My goal is to change the t-sql 2012 listed below so that it updates the
Milestone.[TOT_ABSENCES] field with the value sum(Details.[TOT_ABSENCES]).
Here is the sql that works correctly on a select statement:
select Milestone.SCHOOLNUM,Milestone.STULINK,Milestone.SCHOOLYEAR
,sum(Details.[TOT_ABSENCES]) as TOT_ABSENCES
from Milestone Milestone
join Details Details
on Details.SCHOOLNUM =Milestone.SCHOOLNUM
and Details.STULINK =Milestone.STULINK
and Details.SCHOOLYEAR =Milestone.SCHOOLYEAR
where Milestone.MILESTONE_CODE= '005'
and Details.schoolyear = (select max(schoolyear) FROM Semester)
group by Milestone.SCHOOLNUM,Milestone.STULINK,Milestone.SCHOOLYEAR
However I get an error message about does not like the group by on the following sql:
update Milestone
set TOT_ABSENCES = sum(Details.[TOT_ABSENCES])
from Milestone Milestone
join Details Details
on Details.SCHOOLNUM =Milestone.SCHOOLNUM
and Details.STULINK =Milestone.STULINK
and Details.SCHOOLYEAR =Milestone.SCHOOLYEAR
where Milestone.MILESTONE_CODE= '005'
and Details.schoolyear = (select max(schoolyear) FROM Semester)
group by Milestone.SCHOOLNUM,Milestone.STULINK,Milestone.SCHOOLYEAR
Thus could you show me and/or give me possible suggestions on how to modify the
sql I am referring to?