Hello Matt, > I've got an SQL question. I have a database that contains Reviews of some > of a clients publications. The client is interested in generating some > summary reports and I simply can't wrap my head around a single SQL query > (if that's possible) that will generate the information I'm looking for. > > The one they are most interested in is this... We have a table (built on > user submissions) that has something like this... > > DocName DocSubj Usefulness > Processors Upgrading 1 > Processors Upgrading 2 > Hard Drives Upgrading 1 > Printers Reviews 3 > > They want to get something like this... > > Subject # Reviews Avg Rating > Upgrading 3 1.3 > Reviews 1 1 > > And I'm just missing something... I'm sure it has to possible but I can't > think of good way to do this. (The only way I can think of is to run one > query to grab all the distinct subjects, loop over that resultset and then > fetch the count and average for that particular subject on each iteration)
You seem to have a handle on AVG() and COUNT(). In order to make use of them for less than the whole table's contents, you will have to sub-divide the rows. Check out the GROUP BY clause. It can thus be done in one query/pass! If you're also new to ORDER BY, you might find that useful to sequence the output report. Regards, =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
