TrainingPerfEvalTrainer

DECLARE @ReportID INT = 3481
DECLARE @ReportTypeID INT = 34

SELECT  TRAO.ID,
    TRAO.Description,
    TRAO.VALUE,
    TRAO.ValueAnswer,
    (
        SELECT COUNT(*) FROM Training_ReportAnswer AS TRA
                INNER JOIN Training_ReportQuestion AS TRQ
                    ON TRQ.ID = TRA.ReportQuestionId
            WHERE TRA.ReportID IN
                (
                    SELECT TR.ID FROM Training_Report TR
                    WHERE TR.SessionID =
                        (
                            SELECT TR1.SessionID FROM Training_Report TR1
                                WHERE   TR1.ID = @ReportID
                                    AND ISNULL(TR1.NUT,0) = ISNULL(TRA.NUT,0)
                        )
                        AND TR.ReportTypeID = @ReportTypeID
                )
                AND ISNUMERIC(TRA.Answer) = 1 AND LEN(TRA.Answer) <= 1
                AND TRA.Answer = TRAO.ValueAnswer
    ) AS AnswerCounter,
    (
        TRAO.VALUE *
            (
                SELECT COUNT(*) FROM Training_ReportAnswer AS TRA
                        INNER JOIN Training_ReportQuestion AS TRQ
                            ON TRQ.ID = TRA.ReportQuestionId
                    WHERE TRA.ReportID IN
                        (
                            SELECT TR.ID FROM Training_Report AS TR
                                WHERE TR.SessionID =
                                    (
                                        SELECT TR1.SessionID FROM Training_Report AS TR1
                                            WHERE   TR1.ID = @ReportID
                                                AND ISNULL(TR1.NUT,0) = ISNULL(TRA.NUT,0)
                                    )
                                    AND TR.ReportTypeID = @ReportTypeID
                        )
                        AND ISNUMERIC(TRA.Answer) = 1 AND LEN(TRA.Answer) <= 1
                        AND TRA.Answer = TRAO.ValueAnswer
             )
    ) AS TotalByScale

FROM Training_ReportAnswerOptions TRAO
GROUP BY TRAO.ID, TRAO.Description, TRAO.VALUE, TRAO.ValueAnswer