SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Best way to Calculate Averages??

    I could use some advice on whether there is a more streamlined way to get averages directly from MySQL.

    I have this sample table....

    Code:
    rater	comment		commenter	score	date
    ------	-------		----------	-----	-----
    user1	1001		doubledee	2	2013-09-16
    user1	1002		doubledee	2	2013-09-17
    user2	1002		doubledee	1	2013-09-17

    I need to calculate two averages...

    First, the Average Score of each Comment in the table, for example...
    Code:
    comment		avg score
    --------	----------
    1001		2
    1002		1.5

    Secondly, the Average Score for each Commenter in the table, for example...
    Code:
    commenter	avg score
    ----------	----------
    doubledee	1.666

    I was planning on using aggregate functions like SUM and COUNT and then calculate the AVG using PHP, but maybe this can all be done in SQL?

    Also, it just occurred to me that maybe I need to use a GROUP BY function??

    Getting confused on what initially looked like easy calculations...

    Sincerely,


    Debbie

  2. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,278
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    "First make it work. Then make it better."

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Also, it just occurred to me that maybe I need to use a GROUP BY function??
    yes, you had better test that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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