SQL Server calculate AVG() using window functions

Question

I need to create a report for a survey application, The survey form has 10 Questions, Each question has 4 options.

My report should display QuestionName, Option, TotalUsers who selected that option, Percentage of users who selected that option

I have issue calculating the percentage as my percentage should be percentage of Total sum users only by Question not on whole TotalUsers column

Select V.[Question] as [Question], O.[Option] AS [Option], 
(SELECT COUNT(R.[ResponseId]) FROM dbo.[Response] R 
LEFT JOIN dbo.[Employee] E ON R.[EmployeeId] = R.[EmployeeId]
WHERE R.[OptionId] = O.[OptionId] AND E.[IsActive] = 1 AND E.[RoleId] = @RoleId) 
AS [TotalUsers]
FROM    dbo.[VitalSignQuestions] V
LEFT JOIN   dbo.[VitalSurveyOptions] O ON   V.[QuestionId] = O.[QuestionId]

[TotalUsers] is calculated from a subquery

Answer 1

didn't test but I think here is what you need.

• first there is mistake on condition on joining Employee : LEFT JOIN dbo.[Employee] E ON R.[EmployeeId] = R.[EmployeeId] , so I fixed that
• second, tried to reformat your query a bit,here is final , let me know if it works or not :

SELECT
    V.[Question] AS [Question]
    , O.[Option] AS [Option]
    , COUNT(R.[ResponseId]) AS [TotalUsers]
    , COUNT(R.[ResponseId]) * 100 / (SELECT COUNT(*) FROM  dbo.[VitalSignQuestions] v2 WHERE v2.[QuestionId] = V.[QuestionId]) AS [Percentage]
FROM
    dbo.[VitalSignQuestions]           V
    LEFT JOIN dbo.[VitalSurveyOptions] O
        ON V.[QuestionId] = O.[QuestionId]
    LEFT JOIN dbo.[Response]           R
        ON R.[OptionId] = O.[OptionId]
    LEFT JOIN dbo.[Employee]           E
        ON R.[EmployeeId] = E.[EmployeeId]
           AND E.[IsActive] = 1
           AND E.[RoleId] = @RoleId
GROUP BY
V.[Question]
, O.[Option]