Select a complex count not working as intended

Marius Vasile

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Basit Nisar2y ago
you can use try catch block then try to update the query and catch you error in the table
+1

Marius Vasile2y ago

Ok, got it work with

CREATE PROCEDURE [dbo].[NivelRisc] 

@Status as nvarchar(10),
@AID as int 

AS 

DECLARE @result decimal(6,2),@S7 int,@S6 int,@S5 int,@S4 int,@S3 int,@S2 int,@S1 int

SET
	@S7 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 7 AND AID = @AID
	)
SET
	@S6 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 6 AND AID = @AID
	)
SET
	@S5 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 5 AND AID = @AID
	)
SET
	@S4 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 4 AND AID = @AID
	)
SET
	@S3 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 3 AND AID = @AID
	)
SET
	@S2 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 2 AND AID = @AID
	)
SET
	@S1 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 1 AND AID = @AID
	)
SET
	@result = CONVERT(
		decimal(6, 2),
		((@S7 * 49) + (@S6 * 36) + (@S5 * 25) + (@S4 * 16) + (@S3 * 9) + (@S2 * 4) + @S1)) 
		/ CONVERT(
		decimal(6, 2),
		((@S7 * 7) + (@S6 * 6) + (@S5 * 5) + (@S4 * 4) + (@S3 * 3) + (@S2 * 2) + @S1))
BEGIN
UPDATE
	tblActivitate
SET
	Status = @Status,
	NivelRisc = @result
WHERE
	AID = @AID
END

Marius Vasile2y ago

I managed to build a store procedure with no error but the update is not hapening. Can somebody please tell me why?

CREATE PROCEDURE [dbo].[NivelRisc] 

@Status as nvarchar(10),
@AID as int 

AS 

DECLARE @result int,
@S7 int,
@S6 int,
@S5 int,
@S4 int,
@S3 int,
@S2 int,
@S1 int

SET
	@S7 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 7
	)
SET
	@S6 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 6
	)
SET
	@S5 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 5
	)
SET
	@S4 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 4
	)
SET
	@S3 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 3
	)
SET
	@S2 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 2
	)
SET
	@S1 = (
		SELECT
			COUNT(*)
		FROM
			tblEvaris
		WHERE
			Risc = 1
	)
SET
	@result = CONVERT(
		decimal(4, 2),
		((@S7 * 49) + (@S6 * 36) + (@S5 * 25) + (@S4 * 16) + (@S3 * 9) + (@S2 * 4) + @S1)) 
		/ CONVERT(
		decimal(4, 2),
		((@S7 * 7) + (@S6 * 6) + (@S5 * 5) + (@S4 * 4) + (@S3 * 3) + (@S2 * 2) + @S1))
BEGIN
UPDATE
	tblActivitate
SET
	Status = @Status,
	NivelRisc = @result
WHERE
	AID = @AID
END