Announcement

Collapse
No announcement yet.

Average Query Results

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Average Query Results

    Ok, here's another one from the noob. I am trying to run a query on a column that contains integers and return the average from that query. Obviously the first step is to sum the results and then divide it by the number of rows returned, but I am having some troubles.

    PHP Code:
     $query1 mysql_query("SELECT results FROM test WHERE foo='blah' AND fooblah='blahfoo'");
    $list mysql_num_rows($query1);
    $row1 mysql_fetch_array($query1);
    $totalresults array_sum($row1) / $list;
    echo 
    "$totalreults"
    Thanks,
    Scott Z.
    sigpic
    Nation of Blue - Kentucky Wildcats Sports


    Some CMS Goodness: Add Avatar to Article

  • #2
    Code:
    $query = "SELECT average(column) AS columnaverage FROM test WHERE foo='blah' AND fooblah='blahfoo'";
    ....
     
    Reference $result[columnaverage]

    Comment


    • #3
      Hey merk!

      I appreciate your help, however I had no luck.

      PHP Code:
      $query "SELECT average(results) AS resultsaverage FROM test WHERE userid='$User[userid]' AND test_type='$testtype'";
      $totalaverage $result["resultsaverage"];
      echo 
      "$totalaverage"
      Thoughts?
      Scott Z.
      sigpic
      Nation of Blue - Kentucky Wildcats Sports


      Some CMS Goodness: Add Avatar to Article

      Comment


      • #4
        You have to use the relevant mysql fetching functions.

        PHP Code:
        $connection=mysqlconnect();
        $query=mysqlquery($querytxt$connection);
        [
        b]$array=mysql_fetch_result($query);[/b]
         
        echo 
        $array[averagecolumn]; 
        The bold part might not be totally correct.

        Comment


        • #5
          It's avg() not average()

          Comment


          • #6
            Thanks merk and TiLaser, however I still have issues.

            First of all, we are connecting to the database, I am just omitting that from my code. The query works in phpmyadmin but I can't get it to work in my code. Here is what I have now:

            PHP Code:
            $query mysql_query("SELECT avg(results) AS columnaverage FROM test WHERE userid='1' AND test_type='Calcium'");
            $array mysql_fetch_result($query);
            $totalaverage $array[columnaverage];
            echo 
            "$totalaverage"
            I get a Fatal Error, call to undefined function mysql_fetch_result. If I try mysql_fetch_array() it's an invalid resource.

            Thanks again for your help,
            Scott Z.
            sigpic
            Nation of Blue - Kentucky Wildcats Sports


            Some CMS Goodness: Add Avatar to Article

            Comment


            • #7
              Originally posted by reefland
              Thanks merk and TiLaser, however I still have issues.

              First of all, we are connecting to the database, I am just omitting that from my code. The query works in phpmyadmin but I can't get it to work in my code. Here is what I have now:

              PHP Code:
              $query mysql_query("SELECT avg(results) AS columnaverage FROM test WHERE userid='1' AND test_type='Calcium'");
              $array mysql_fetch_result($query);
              $totalaverage $array[columnaverage];
              echo 
              "$totalaverage"
              I get a Fatal Error, call to undefined function mysql_fetch_result. If I try mysql_fetch_array() it's an invalid resource.

              Thanks again for your help,
              Scott Z.
              Use mysql_fetch_row()

              Comment


              • #8
                Originally posted by merk
                Use mysql_fetch_row()
                Thanks merk and beleive it or not but I finally got it!

                PHP Code:
                $query mysql_query("SELECT avg(results) AS columnaverage FROM test WHERE userid='$User[userid]' AND test_type='$testtype'");
                $row mysql_fetch_array($query);
                $totalresults $row["columnaverage"];
                echo 
                "$totalresults"
                I don't know why I had so many troubles with that but I appreciate your continued help!

                Now, here is a follow-up question. The results of the query display 418.333333, how can I limit this to only show the hundreths (only 2 spots after the decimal)?

                Thanks a million for helping out this beginner,
                Scott Z.
                sigpic
                Nation of Blue - Kentucky Wildcats Sports


                Some CMS Goodness: Add Avatar to Article

                Comment


                • #9
                  I didnt help you with the mysql functions because you didnt ask for that. mysql_fetch_row is a better fuction to do what you want to do.

                  And of course, for rounding i hope you checked out www.php.net/round before asking

                  Comment


                  • #10
                    Hey merk!

                    Yeah I looked at php.net at round and actual found that using number_format() is probably a better way to go for me because in some cases, I want 400.00 to be shown and number_format() allows me to set the number of numbers after the decimal.

                    Thanks again for your help!
                    Scott Z.
                    sigpic
                    Nation of Blue - Kentucky Wildcats Sports


                    Some CMS Goodness: Add Avatar to Article

                    Comment

                    widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                    Working...
                    X