I am trying to implement an weighted average into a select query. A normal average is working well but the weighted one is not. The normal average, working is:
BEGIN UPDATE tblActivitate SET Status = @Status, NivelRisc = (SELECT CONVERT(decimal(4,2), Sum(Risc))/CONVERT(decimal(4,2), Count(Risc)) AS NivelRisc FROM tblEvaris WHERE AID = @AID) WHERE AID = @AID END
The weighted average has an error "unresolved refference to an object"
BEGIN UPDATE tblActivitate SET Status = @Status, NivelRisc = (SELECT CONVERT(decimal(4,2), Sum( ((CASE Risc WHEN "7" THEN 1 ELSE 0 END)*49), ((CASE Risc WHEN "6" THEN 1 ELSE 0 END)*36), ((CASE Risc WHEN "5" THEN 1 ELSE 0 END)*25), ((CASE Risc WHEN "4" THEN 1 ELSE 0 END)*16), ((CASE Risc WHEN "3" THEN 1 ELSE 0 END)*9), ((CASE Risc WHEN "2" THEN 1 ELSE 0 END)*4), ((CASE Risc WHEN "1" THEN 1 ELSE 0 END)*1) )) /CONVERT(decimal(4,2),Sum( ((CASE Risc WHEN "7" THEN 1 ELSE 0 END)*7), ((CASE Risc WHEN "6" THEN 1 ELSE 0 END)*6), ((CASE Risc WHEN "5" THEN 1 ELSE 0 END)*5), ((CASE Risc WHEN "4" THEN 1 ELSE 0 END)*4), ((CASE Risc WHEN "3" THEN 1 ELSE 0 END)*3), ((CASE Risc WHEN "2" THEN 1 ELSE 0 END)*2), ((CASE Risc WHEN "1" THEN 1 ELSE 0 END)*1) )) AS NivelRisc FROM tblEvaris WHERE AID = @AID) WHERE AID = @AID END
Error
SQL71501: Procedure: [dbo].[UpdateNivelRisc] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[tblActivitate].[7] or [dbo].[tblEvaris].[7].
The difference between both select is the Sum part which is correct, or I think so