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. ---------------------------------------------------------------------------- ----------------- The other example would avg by comment which i suppose doesnt make sense no looking at it... Average by Article is what you really wanted ... SELECT ArticleID, AVG(Rating) as Rating FROM Comments GROUP BY ArticleID Going back to your original question about making a table that stores the averages.. that would be against RDMS theory. You arent supposed to have the same information twice in a DB, and you should strive to store the information at the atomic (lowest) level. Having said that people do what you describe in Data Warehouses but these are usually for high end DB's with 1,000,000's of rows (and they are a pain to maintain) That should do the trick. Give it a go and see what happens. It would be a good idea to have a look at some RDBMS + SQL books to learn about aggregates and standard SQL features as it will help you a lot. The SQL tute http://w3.one.net/~jhoffman/sqltut.htm looks quite good. Covers AVG() I dont now of any good tutes on RDBMS design but have a search for "Normal Form" or "Normalization" and "RDBMS" and see what you come up with. Have fun. mn 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: Friday, November 17, 2000 6:18 AM To: Nold, Mark Subject: Re: Average? SELECT CommentID(key), AVG(Rating) as Rating FROM Comments GROUP BY CommentID The problem is that I need to combine the ratings for each individual article, to get an average... and there are multiple articles in the table (hence the ArticleID) Wouldn't this Select clause average everything together? ----- Original Message ----- From: "Nold, Mark" <Mark.Nold@normandy.com.au> To: "'Jeremy'" <sparhawk@cleanweb.net>; <php-db@lists.php.net> Sent: Thursday, November 16, 2000 12:33 AM Subject: 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?