RE: Average?

---------------------------------------------------------------------------- ----------------- Disclaimer: The information contained in this email is intended only for the use of the person(s) to whom it is addressed and may be confidential or contain legally privileged information. If you are not the intended recipient you are hereby notified that any perusal, use, distribution, copying or disclosure is strictly prohibited. If you have received this email in error please immediately advise us by return email at postmaster@normandy.com.au and delete the email document without making a copy. ---------------------------------------------------------------------------- ----------------- I would assmue most RDBMS have a AVG() or AVERAGE() or MEAN() aggregate function (i havent done one for some time so check your docs. You dont need the second table as it will always be out of date. Try something like; SELECT CommentID(key), AVG(Rating) as Rating FROM Comments GROUP BY CommentID or if no supported average functions try... SELECT CommentID(key), SUM(Rating) as Rating, COUNT(*) as theCount FROM Comments GROUP BY CommentID Now maybe you can divide the SUM by the COUNT but im not sure of the effect this would have... SELECT CommentID(key), SUM(Rating) as Rating, COUNT(*) as theCount, SUM(Rating) / COUNT(*) as theAverage FROM Comments GROUP BY CommentID Mark Nold markn@enspace.com <mailto:markn@enspace.com> Senior Consultant   Change is inevitable, except from vending machines. -----Original Message----- From: Jeremy [mailto:sparhawk@cleanweb.net] Sent: Thursday, November 16, 2000 8:49 AM To: php-db@lists.php.net Subject: Average? I am making an area of the site where members can rate specific resources. They can add a comment for the rating. I am trying to make a seperate table that contains the average of all the ratings, so I can easily display the top-ranked articles. Here is my table structure : Table Comments CommentID(key) ArticleID User Comment Rating Table Rating ArticleID Article_Name Rating Right now, I am trying to find out how to get an average of all the ratings of the specified ArticleID, and then insert it into the Ratings Table. I tried to use numrows to find what I should divide by, but if I fetch my results in an array, I can divide $rate["rating"] by 5.... What are the solutions to this?