SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql - avg colums, then find the average of that ....

    Here is a very simplified version of what I am trying to do...
    Code:
    SELECT
    COUNT(*) AS qty,
    AVG(p1) AS val1,
    AVG(p2) AS val2, 
    AVG(p3) AS val3
    FROM `test_db`
    WHERE cid = 5
    This query finds all records in my "test_db" with a "cid" value of 5. It then counts how many records, and then averages each column so I can display as needed...

    Example Results
    qty = 7
    val1 = 3.2
    val2 = 4.3
    val3 = 3.1

    MY PROBLEM:
    Now I need to take all the average values (val1, val2 & val3 but not the qty) and get the average from those and save it within another value (eg: totalaverage)...
    I would like to do this within the query if possible, and not use any php scripting for that final calculation.

    The results I would like are:
    qty = 7
    val1 = 3.2
    val2 = 4.3
    val3 = 3.1
    totalaverage = 3.53

    One additional problem. It is possible that some of the values (val1, val2, val3) are actually null, therefore it will not be as easy as just adding/dividing.

    Is there any way to do this within MySQL? Or must I resort to doing that final calculation with PHP?

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select count(*) as qty,
           avg(p1) as val1,
           avg(p2) as val2, 
           avg(p3) as val3
           (avg(p1) + avg(p2) + avg(p3))/3.0 as totalaverage
      from test_db
     where cid = 5

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    Code:
    select count(*) as qty,
           avg(p1) as val1,
           avg(p2) as val2, 
           avg(p3) as val3
           (avg(p1) + avg(p2) + avg(p3))/3.0 as totalaverage
      from test_db
     where cid = 5
    Excellent - that was really close, but it was not considering that some of the values could have been null.
    It's ok to add everything, including the null's - but a NULL result should not be part of the division. In this case - if P2 was null, then it should be divided by 2, not 3.

    Based on your sample though - I was able to come up with something that should work.
    Code:
    select count(*) as qty,
           avg(p1) as val1,
           avg(p2) as val2, 
           avg(p3) as val3,
           (avg(p1) + avg(p2) + avg(p3))/(IF(ISNULL(p1), 0, 1) + IF(ISNULL(p2), 0, 1) + IF(ISNULL(p3), 0, 1)) as totalaverage
    from test_db
    where cid = 5
    If anyone can see a better way to do this, kindly let me know...
    @swampBoogie - Thanks for nudging me in the right direction... I appreciate it.

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It turns out that NULL values can cause some troubles when adding columns with the + operator. My solution here was to check for null and replace with 0 when found. At the same time, my division checks were also incorrect, as they were not checking the "avg" of each column. Therefore I was getting unpredictable results.

    Here is the completed SQL that has tested positive in all my tests so far.
    Code:
    select count(*) as qty,
    IFNULL(AVG(p1),0) as val1,
    IFNULL(AVG(p2),0) as val2, 
    IFNULL(AVG(p3),0) as val3,
    (IFNULL(AVG(p1),0) + IFNULL(AVG(p2),0) + IFNULL(AVG(p3),0))/(IF(ISNULL(avg(p1)), 0, 1) + IF(ISNULL(avg(p2)), 0, 1) + IF(ISNULL(avg(p3)), 0, 1)) as totalaverage
    FROM `test_db`
    WHERE cid = 5


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
  •