"Bill Adams" <[EMAIL PROTECTED]> wrote on 10/28/2005 01:49:28 PM:
> All, > > In the following query, some of the values are averaged over several > rows, but some are not: > > SELECT hostname, volname, qtreename, round(avg(used/allocated*100),0), > round(avg(used)), allocated, available > FROM quota_entries > WHERE date_sub(now(), interval 1 day) < timestamp > GROUP BY qtreename,hostname > > My questions: > From which of the several averaged rows do 'allocated' and 'available' > come in the results? > Can I control this? I would like the row with maximum timestamp. > > Thanks, > Bill > > +-----------+------------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +-----------+------------------+------+-----+---------+----------------+ > | id | int(10) unsigned | | PRI | NULL | auto_increment | > | hostname | varchar(32) | | | | | > | volname | varchar(32) | | | | | > | qtreename | varchar(32) | | | | | > | allocated | int(10) unsigned | | | 0 | | > | used | int(10) unsigned | | | 0 | | > | available | int(10) unsigned | | | 0 | | > | files | int(10) unsigned | YES | | 0 | | > | timestamp | datetime | YES | MUL | NULL | | > +-----------+------------------+------+-----+---------+----------------+ > I do not think you are posing a single-statment question to the database (I count subqueries as second statements). You would like to find the averages of (used/allocated) and (used) for all (qtreename,hostname) pairs. That's one question. The second question is to return the row with the greatest timestamp for each (qtreename,hostname) pair along with the averages calculated in the first question. To find the answer your first question is a simple GROUP BY query. To find the answer to your second takes a max-of-group-pattern query: http://dev.mysql.com/doc/refman/4.1/en/examples.html If it were me, I would use a temporary table for each stage and combine them to form the final report. If you need more help, just come back. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
