3
Answers

Select a complex count not working as intended

Marius Vasile

Marius Vasile
1y
524
1

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
 

Answers (3)
Next Recommended Forum