July 7, 2019 at 4:07 am
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:
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?
July 7, 2019 at 6:48 am
You need to use a CTE or a sub-query to generate your aggregates, and then join to that for the update
WITH cteUpdate as (
select Milestone.SCHOOLNUM, Milestone.STULINK, Milestone.SCHOOLYEAR
, sum(Details.[TOT_ABSENCES]) as TOT_ABSENCES
from Milestone Milestone
inner 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
)
UPDATE ms
SET TOT_ABSENCES = upd.TOT_ABSENCES
FROM Milestone AS ms
INNER JOIN cteUpdate AS upd
ON ms.SCHOOLNUM = cte.SCHOOLNUM
AND ms.STULINK = cte.STULINK
AND ms.SCHOOLYEAR = cte.SCHOOLYEAR;
July 7, 2019 at 7:54 pm
that answers my question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply