sum as per column and row values in ms sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,
I have a table with following data.

SQL

Id   CorrectOption   OptionAnswered   RelationId

 1        2               4             10
 2        1               1             10
 3        1               2             10
 4        3               3             10
 5        4               4             10

Here I need to compare with CorrectOption column with OptionAnswered column.
I need output as 60% correct (3 options correct).

Thanks
Suresh

Posted 4-Mar-14 2:51am

itsureshuk

Add a Solution

Comments

Ramug10 4-Mar-14 9:04am

have you tried anything..

Andrius Leonavicius 4-Mar-14 9:16am

So what have you tried so far? You must try something first. If you stuck somewhere, then we could help you...

itsureshuk 4-Mar-14 10:22am

I have tried with while loop in stored procedure
declare @totalrow int
declare @total int
declare @Count int
declare @CorrectVal int
declare @AnsweredVal int
set @totalrow=10
set @Count=1
set @total=0
while @Count<=@totalrow
BEGIN
SELECT @CorrectVal=OptionAnswered,@AnsweredVal=CorrectAnswer FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY AnswerAttendId) AS rownumber,
OptionAnswered,CorrectAnswer
FROM Tb1where RelationId=10
) AS temptb
WHERE rownumber = @Count
--select @CorrectVal,@AnsweredVal
if(@CorrectVal=@AnsweredVal)
BEGIN
set @total=@total+1
--select @total
END
set @Count=@Count+1
END
select @total as total
select @totalrow as totalrow
set @total=100*(@total/@totalrow)

Here is my code its working but when i try to get set @total=100*(@total/@totalrow)
showing Zero

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-03-04T03:41:00

Have a look at example:

SQL

DECLARE @tmp TABLE (Id INT IDENTITY(1,1),  CorrectOption INT,  OptionAnswered INT,  RelationId INT)

INSERT INTO @tmp (CorrectOption, OptionAnswered, RelationId)
VALUES(2, 4, 10), (1, 1, 10), (1, 2, 10), (3, 3, 10), (4, 4, 10)


SELECT RelationId, ProperAnswers, CountOfAnswers, CONVERT(INT, (CONVERT(DECIMAL(8,2), ProperAnswers)/CONVERT(DECIMAL(8,2),CountOfAnswers))*100) + '%' AS [%]
FROM (
    SELECT t1.RelationId, COUNT(t1.RelationId) As CountOfAnswers, t2.ProperAnswers
    FROM @tmp AS t1 INNER JOIN (
        SELECT RelationId, COUNT(RelationId) AS ProperAnswers
        FROM @tmp
        WHERE CorrectOption = OptionAnswered
        GROUP BY RelationId
        ) AS t2 ON t1.RelationId  = t2.RelationId
    GROUP BY t1.RelationId, t2.ProperAnswers
    ) AS T

Result:

Rel..   PropA   CountA  %
10	3	5	60%