Why is this average calculating incorrectly?

Question

So I have two procedures

CREATE PROCEDURE GetAnswersByPartner 
    @pid UNIQUEIDENTIFIER, -- partner id
    @sid INT -- survey id
AS
BEGIN
    SELECT Sections.title AS section_title,
    Subsections.title AS subsection_title,
    Questions.id AS question_id,
    Questions.qtext AS question_text,
    Answers.val AS answer_val 
    FROM Partners
    INNER JOIN Questions ON 1 = 1
    INNER JOIN Subsections ON Subsections.id = Questions.subsection_id
    INNER JOIN Sections ON Sections.id = Subsections.section_id
    INNER JOIN Surveys ON Sections.survey_id = Surveys.id
    LEFT JOIN Answers ON Answers.partner_id = Partners.id
            AND Answers.question_id = Questions.id
    WHERE Partners.id = @pid AND Surveys.id=@sid
    ORDER BY Sections.id ASC
END

and

CREATE PROCEDURE GetSectionAverages
    @partner_id UNIQUEIDENTIFIER,
    @survey_id INT
AS
    BEGIN
        SELECT S.title, AVG(A.val)   
        FROM 
            Sections AS S INNER JOIN Subsections AS SS ON S.Id=SS.section_id
                          INNER JOIN Questions AS Q ON SS.section_id=Q.subsection_id
                          INNER JOIN Answers AS A ON A.question_id=Q.id
                          INNER JOIN Partners AS P ON A.partner_id=P.id
            WHERE S.survey_id=@survey_id AND P.id=@partner_id
            GROUP BY S.title
    END

and I can't figure out why the averages are calculating incorrectly.

For example, you can see in the screenshot below the result of EXEC GetAnswersByPartner @pid='cd2c53e2-3bd4-451c-8451-6953b9cc7c8b', @sid=1; in the section called "Partner Growth"

and that is calculating out to 100 as an average when I run

EXEC GetSectionAverages @partner_id='cd2c53e2-3bd4-451c-8451-6953b9cc7c8b', @survey_id=1;

Any idea what's going on here?

Answer 1

Your first query has a LEFT JOIN to the Answers table, while your second has an INNER JOIN. I'd suspect that the difference lies there. In other words, your second query may be excluding unanswered questions.