代码之家 › 专栏 › 技术社区 › K.Z

在T-SQL中,如何对同样在Count方法中计算的行值求和

tsql sql-server

K.Z · 技术社区 · 6 年前

我正在编写T-SQL脚本,其中有一个列的和值,现在我需要添加所有行的值并使用它来计算百分比。我的问题是如何求所有行中的值的和,因为这一行也有count()

SELECT 
    q.Id AS QuestionId,
    ansOpt.QuestionOptionId AS QuestionOptionId,
    qOpt.Value,
    COUNT(ansOpt.QuestionOptionId) AS QuestionOptionCount,
    (((CONVERT(DECIMAL, COUNT(ansOpt.QuestionOptionId)))) / 8) * 100 AS AnswerCoutPercentage
    -- COUNT(QuestionOptionCount) // need help here 
FROM 
    [dbo].[Responses] AS res 
GROUP BY 
    q.Id, ansOpt.QuestionOptionId, qOpt.Value

1 回复 | 直到 6 年前

K.Z 6 年前

DECLARE @totalAnswer INT = 0;
SELECT @totalAnswer = SUM(a.TotalCount)
    FROM 
        (SELECT COUNT(ansOpt.QuestionOptionId) AS TotalCount
            FROM [dbo].[Responses] AS res 
        INNER JOIN [dbo].[Surveys] AS sur ON res.SurveyId = sur.Id  
        INNER JOIN [dbo].[Answers] AS ans ON res.Id = ans.ResponseId
        INNER JOIN [dbo].[AnswerOptions] AS ansOpt ON ans.Id = ansOpt.AnswerId
        WHERE sur.ConsultationId = '00000001-0012-4D34-95D8-0000000000'
        AND ans.QuestionId = '0ADED4CE-5385-5826-4880-00000000000'
        GROUP BY ansOpt.QuestionOptionId) AS a

推荐文章