Update statement error

[titans4771]

I am getting the below error:

An aggregate may not appear in the set list of an UPDATE statement.


When trying to do this:

UPDATE t
SET [StaffServPay] = SUM(case when d.paycodeid = 1 and computeonunits = 1 then (d.actualunits*d.payrate)
when d.paycodeid = 1 then ISNULL(d.payrate,0) ELSE 0 end),
[CheckPay] = SUM(case when c.paycodeid = 1 and computeonunits = 1 then (c.actualunits*c.payrate)
when c.paycodeid = 1 then c.payrate ELSE 0 end),
[StaffServBill] = SUM(case when d.paycodeid = 1 and computeonunits = 1 then (d.billunits*d.billrate)
when d.paycodeid = 1 then ISNULL(d.billrate,0) ELSE 0 end),
[StaffServOTPay] = SUM(case when d.paycodeid = 2 and computeonunits = 1 then (d.actualunits*d.payrate)
when d.paycodeid =2 then ISNULL(d.payrate,0) ELSE 0 end),
[CheckOT] = SUM(case when c.paycodeid = 2 and computeonunits = 1 then (c.actualunits*c.payrate)
when c.paycodeid = 2 then c.payrate ELSE 0 end),
[StaffServSalaryPay] = SUM(case when d.paycodeid = 7 and computeonunits = 1 then (d.actualunits*d.payrate)
when d.paycodeid = 7 then d.payrate ELSE 0 end),
[StaffServSalaryBill] = SUM(case when d.paycodeid = 7 and computeonunits = 1 then (d.billunits*d.billrate)
when d.paycodeid = 7 then d.billrate ELSE 0 end),
[StaffServExpPay] = SUM(case when s.expenseflag = 1 and computeonunits = 1 then (d.actualunits*d.payrate)
when s.expenseflag = 1 then d.payrate ELSE 0 end),
[StaffServExpBill] = SUM(case when s.expenseflag = 1 and computeonunits = 1 then (d.billunits*d.billrate)
when s.expenseflag = 1 then d.billrate ELSE 0 end),

FROM @TimeCardInfo t
LEFT OUTER JOIN integrateprod.dbo.intxref i3 ON i3.intid = t.intid and i3.fieldid = 477 and i.subscriberid = 3
LEFT OUTER JOIN ss_htrnj.dbo.vw_tcheaderall t on t.tcid = i3.extid
LEFT OUTER JOIN ss_htrnj.dbo.vw_tcdetailall d (NOLOCK) on d.tcid = t.tcid
LEFT OUTER JOIN ss_htrnj.dbo.prchecktc c (NOLOCK) on c.tcdetailid = d.tcdetailid
LEFT OUTER JOIN ss_htrnj.dbo.syspaycodes s (NOLOCK) on s.paycodeid = d.paycodeid

Please advise how to update a table column with aggregate values?

Thanks,

 

[markros]

What is your version of SQL Server? In your update command you're missing GROUP BY, but I'm not sure how exactly you want to group by.

Alternatively, you may want to first grab information into cte using select statement and then update.

 

[titans4771]

I am using 2000? I left out the first part of the query, whcih has my select statement:

DECLARE @TimeCardInfo Table (intid int, fieldid int, extid int, timesheetid int, tcid int, firstname varchar(50), lastname varchar(50),TimeSheetPay decimal(19,6), StaffServPay decimal(19,6), CheckPay decimal(19,6),
TimeSheetBill decimal (19,6), StaffServBill decimal(19,6), TimeSheetOTPay decimal(19,6), StaffServOTPay decimal(19,6), CheckOT decimal(19,6),
TimeSheetOTBill decimal(19,6), StaffServOTBill decimal(19,6), TimeSheetSalary decimal(19,6), StaffServSalaryPay decimal(19,6),
TimeSheetSalaryBill decimal(19,6), StaffServSalaryBill decimal(19,6), TimeExpPay decimal(19,6), StaffServExpPay decimal(19,6),
CheckExp decimal(19,6), TimeSheetExpBill decimal (19,2), StaffServExpBill decimal(19,6))
INSERT @TimeCardInfo (intid, fieldid, extid, timesheetid, tcid, firstname, lastname, TimeSheetPay, StaffServPay, CheckPay, TimeSheetBill, StaffServBill, TimeSheetOTPay, StaffServOTPay,
CheckOT, TimeSheetOTBill, StaffServOTBill, TimeSheetSalary, StaffServSalaryPay, TimeSheetSalaryBill , StaffServSalaryBill, TimeExpPay, StaffServExpPay,
CheckExp, TimeSheetExpBill, StaffServExpBill)
(
Select intid, fieldid, extid, t.timesheetid , tcid=0, firstname, lastname,

SUM(case when ts.paycodeid = 1 and computeonunits = 1 then (ts.payunits*ts.payrate)
when ts.paycodeid = 1 then ISNULL(ts.payrate,0) ELSE 0 end) as TimeSheetPay,
StaffServPay=0, CheckPay=0,
SUM(case when ts.paycodeid = 1 and computeonunits = 1 then (ts.billunits*ts.billrate)
when ts.paycodeid = 1 then ISNULL(ts.billrate,0) ELSE 0 end) as TimeSheetBill,
StaffServBill=0,
SUM(case when ts.paycodeid = 2 and computeonunits = 1 then (ts.payunits*ts.payrate)
when ts.paycodeid =2 then ISNULL(ts.payrate,0) ELSE 0 end) as TimeSheetOTPay,
StaffServOTPay=0, CheckOT=0,
SUM(case when ts.paycodeid = 2 and computeonunits = 1 then (ts.billunits*ts.billrate)
when ts.paycodeid = 2 then ISNULL(ts.billrate,0) ELSE 0 end) as TimeSheetOTBill,
StaffServOTBill=0, CheckOT=0,
SUM(case when ts.paycodeid = 7 and computeonunits = 1 then (ts.payunits*ts.payrate)
when ts.paycodeid = 7 then ts.payrate ELSE 0 end) as TimeSheetSalary,
StaffServSalaryPay=0,
SUM(case when ts.paycodeid = 7 and computeonunits = 1 then (ts.billunits*ts.billrate)
when ts.paycodeid = 7 then ts.billrate ELSE 0 end) as TimeSheetBill,
StaffServSalaryBill=0,
SUM(case when p.expenseflag = 1 and computeonunits = 1 then (ts.payunits*ts.payrate)
when p.expenseflag = 1 then ts.payrate ELSE 0 end) as TimeSheetExpPay,
StaffServExpPay=0,
SUM(case when p.expenseflag = 1 and computeonunits = 1 then (ts.billunits*ts.billrate)
when p.expenseflag = 1 then ts.billrate ELSE 0 end) as TimeSheetExpBill,
StaffServExpBill=0


from integrateprod.dbo.rmxtimesheet t
left outer join integrateprod.dbo.intxref x2 (NOLOCK) on x2.intid = t.timesheetid and x2.fieldid = 477 and x2.subscriberid = 1
left outer join integrateprod.dbo.rmxtimesheetdetail ts (NOLOCK) on ts.timesheetid = t.timesheetid
left outer join integrateprod.dbo.srcpaycodes p on p.paycodeid = ts.paycodeid
left outer join rmxprod.dbo.candidates c on c.candidateid = t.candidateid
where t.createdate between @startdate and @enddate and t.timesheetid = '582403'--
Group by intid, fieldid, extid, t.timesheetid, firstname, lastname
)

Now I need to go back and add in my columns that are set to '0' and that is why I am doing an update statement?