Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Unanswered: Aggregate of an Aggregate

    Sorry to be asking a stupid question by my search results have been futile this far.

    Right now I have a list of Surveys and a list of Responses to each survey. What I need to do is get the average response of scores by survey. Which is to say I need to know the score for each survey and then average those results to make sure that no matter how many responses one survey receives, it carries no more weight than a survey with one response. Apologize if I'm asking an already answered question.

    Pertinent Structure

    tblResponse
    objid
    returnkey
    response

    tblSurvey
    objid


    Current Query:

    SELECT
    SUM(RE.Response) / COUNT(RE.Response) AS SurvScore
    FROM
    tblSurveyData SD INNER JOIN
    tblResponses RE ON RE.Return_Key = SD.objid
    GROUP BY
    SD.objid



    Ideally:

    SELECT
    AVG(SUM(RE.Response) / COUNT(RE.Response) AS AvScore FROM
    tblSurveyData SD INNER JOIN
    tblResponses RE ON RE.Return_Key = SD.objid
    GROUP BY
    SD.objid

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try using an embedded subquery to calculate your statistics in two distinct steps:

    SELECT AVG(SurvScore) AvScore
    from
    (SELECT SUM(RE.Response)/COUNT(RE.Response) AS SurvScore
    FROM tblSurveyData SD
    INNER JOIN tblResponses RE ON RE.Return_Key = SD.objid
    GROUP BY SD.objid) SurveyScores

    blindman

  3. #3
    Join Date
    Nov 2003
    Posts
    17

    Thanks SO much

    Right on the money sir, I can't thank you enough

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •