#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2

    Getting Multiple Averages


    I have a query that gets the average of category 1. Is it possible to make a query that holds the averages of ALL categories? like so..

    Category 1 => 2.3
    Category 2 => 3.1

    Code:
    SELECT AVG(answer) as average
    	FROM answers
    	INNER JOIN questions ON answers.question_id = questions.question_id
    	INNER JOIN categories ON categories.category_id = questions.category_id
    	WHERE categories.category_id = 2");
    Thanks!
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2

    Getting Multiple Averages


    I have a query that gets the average of category 1. Is it possible to make a query that holds the averages of ALL categories? like so..

    Category 1 => 2.3
    Category 2 => 3.1

    Code:
    SELECT AVG(answer) as average
    	FROM answers
    	INNER JOIN questions ON answers.question_id = questions.question_id
    	INNER JOIN categories ON categories.category_id = questions.category_id
    	WHERE categories.category_id = 2");
    Thanks!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    Code:
    SELECT AVG(answer) as average, categories.categoryId 
    	FROM answers
    	INNER JOIN questions ON answers.question_id = questions.question_id
    	INNER JOIN categories ON categories.category_id = questions.category_id
    	group by categories.categoryId 
            order by  categories.categoryId
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Code:
    SELECT categories.category_id
         , AVG(answers.answer) as average
      FROM categories
    INNER
      JOIN questions
        ON questions.category_id = categories.category_id
    INNER
      JOIN answers
        ON answers.question_id = questions.question_id
    GROUP
        BY categories.category_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Thank you very much! I hadn't looked in to the GROUP BY function. Mysql keeps suprising!

IMN logo majestic logo threadwatch logo seochat tools logo