Calculate percentage based on answerid in SQL Server

Refer the below sample test query for your reference and implement it as per your logic.

SQL

CREATE TABLE #QuestionAnswer
( questionid	INT,
  empid			INT,
  answerid 		INT,
  correctanswer	INT
)

INSERT INTO #QuestionAnswer(questionid,empid,answerid,correctanswer)
SELECT 1 ,11111,3,3
UNION ALL SELECT 2,11111,3,2
UNION ALL SELECT 3,11111,1,3
UNION ALL SELECT 4,11111,1,2
UNION ALL SELECT 5,11111,1,4
UNION ALL SELECT 6,11111,1,1
UNION ALL SELECT 7,11111,4,4
UNION ALL SELECT 8,11111,1,1
UNION ALL SELECT 9,11111,4,4
UNION ALL SELECT 10,11111,1,1
UNION ALL SELECT 11,11111,3,3
UNION ALL SELECT 12,11111,4,4
UNION ALL SELECT 13,11111,4,4
UNION ALL SELECT 14,11111,4,4
UNION ALL SELECT 15,11111,2,2
UNION ALL SELECT 16,11111,2,2
UNION ALL SELECT 17,11111,2,2
UNION ALL SELECT 18,11111,2,2
UNION ALL SELECT 19,11111,2,2
UNION ALL SELECT 20,11111,1,1
UNION ALL SELECT 21,11111,2,2
UNION ALL SELECT 22,11111,2,2
UNION ALL SELECT 23,11111,1,1
UNION ALL SELECT 24,11111,1,1
UNION ALL SELECT 25,11111,2,2
UNION ALL SELECT 26,11111,4,4
UNION ALL SELECT 27,11111,2,2
UNION ALL SELECT 28,11111,3,3
UNION ALL SELECT 29,11111,3,3
UNION ALL SELECT 30,11111,1,4


DECLARE @EmployeeId INT
DECLARE @QuestionCount INT
DECLARE @CorrectAnswerCount INT

SET @EmployeeId = 11111
SET @QuestionCount = (SELECT COUNT(*) questionid FROM #QuestionAnswer WHERE empid = @EmployeeId)

SET @CorrectAnswerCount = (SELECT COUNT(*) FROM #QuestionAnswer
						   WHERE answerid = correctanswer
						   AND empid = @EmployeeId) 
  
SELECT @EmployeeId EmployeeId
	  ,@QuestionCount QuestionCount
	  ,@CorrectAnswerCount CorrectAnswer
	  ,CAST(((CAST(@CorrectAnswerCount AS decimal(18,2)) / CAST(@QuestionCount AS decimal(18,2))) * 100) AS DECIMAL(18,2)) Percentage

Screenshot

Logic is same for your question. You just need to get the question no count in @QuestionCount variable also For @CorrectAnswerCount variable From related table with respected count values as per single table query or as per Multiple table using joins. Always be proper with your table structure before posting any question so users can reply you accordingly.

kalpesh says:

SET @QuestionCount = (SELECT COUNT(*) questionid FROM #QuestionAnswer WHERE empid = @EmployeeId)

 

SET @CorrectAnswerCount = (SELECT COUNT(*) FROM #QuestionAnswer

                           WHERE answerid = correctanswer

                           AND empid = @EmployeeId)

Just replace as per your actual calculating table values as per yiur logic.

SET @QuestionCount = (SELECT COUNT(*) QuestionId FROM ResultTable WHERE Emp_Id = @EmployeeId)

SET @CorrectAnswerCount = (SELECT COUNT(*) FROM AnswerTable
                           INNER JOIN ResultTable ON AnswerTable.QuestionId = ResultTable.QuestionId
						   WHERE AnswerTable.AnswerId = ResultTable.AnswerId
						   AND ResultTable.Emp_Id = @EmployeeId)