按ms sql server中的列和行值求和 [英] sum as per column and row values in ms sql server

问题描述

我有一张包含以下数据的表。

Hi,
I have a table with following data.

Id   CorrectOption   OptionAnswered   RelationId

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

这里我需要与CorrectOption列进行比较OptionAnswered列。

我需要输出60％正确（3个选项正确）。



谢谢

Suresh

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

Thanks
Suresh

推荐答案

看看例子：

Have a look at example:

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%

这篇关于按ms sql server中的列和行值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！