Re: aggreate data

[Rick James]

You cannot do
select *, max(used) as maxused, avg(used) as avgused from space where mountpoint='/' group by date with rollup;
in a single step -- the '*' will come from the 'first' row encountered (likely to be the earliest time).

This will work, yes?
select max(used) as maxused, avg(used) as avgused from space where mountpoint='/' group by date with rollup;

select *
    FROM (
        SELECT *
            from space
            where mountpoint='/'
            ORDER BY used DESC
          ) x       -- First do the ORDERing
    GROUP BY date   -- Then pick the one row per day desired

Does that get the '*' and (effectively) the maxused?

Then you wanted two more things; here's one of them:

select  *,
        ( SELECT avg(used) FROM space WHERE date = x.date ) AS avgused 
    FROM ( ... ) x
    GROUP BY date