aggreate data

[Domenic Forgione]

I am trying to extract a value out of a database that has the maximum/average value for each day. There are 24 entries, one per hour for every filesystem.

The table contains: date, time, filesystem, total, used, available, mountpoint.


This table represents the output of the 'df -k' command on a Solaris 10 box. it is imported into a MySQL DB. I need to extract the maximum, average space used for each day for each filesystem. The maximum is the actual value in the database, while average is the average usage that is calculated for the day.

I have tried:

mysql> select *, max(used) as maxused, avg(used) as avgused from space where mountpoint='/' group by date with rollup;


It seems to select the same hour everyday, which is not the case when the maximum occurs for the column used!

Any thoughts?

PS. I have only just begun using MySQL.