At 09:44 16/03/99 -0500, "Graeme B. Davis" <GraemeD@stripped> wrote:
>Does anyone have any insight on this? (I tried selecting avg(count(user) but
>that didn't work :( )
>
>select user, modified, count(user) as count from db where user='graemed'
group
>by modified;
>
>+--------+------------+------------+
>| count | user | modified |
>+--------+------------+------------+
>| 20 | graemed | 1999-02-01 |
>| 17 | graemed | 1999-02-02 |
>| 5 | graemed | 1999-02-03 |
>| 24 | graemed | 1999-02-04 |
>etc...
>
>I want to get a AVG/MIN/MAX of the count column over all the rows.
>
>Ideas?
>
>Thanks!
>
>Graeme
A bit off topic but...
I must admit that I'm a little unclear exactly what you're trying to
acheive - it seems to be a second order consolidation of your data - the
first order being then number of times a particular 'user' did something on
a particular date ('modified'). However I can't see what you want to
perform the second order consoldation by - I'll assume it's by the
'modified' date....
AFAIK this would only be possible by creating a record source from a query
(e.g. a view in Oracle / query in Access) then creating a query based on
that record source. This isn't possible in MySQL so you would need to
generate the intermediate form yourself:
(I'm a bit hazy on the finer points of MySQL's SQL so this may need some
changes)
SELECT user, modified, count(user) AS my_count
INTO my_temp_table
FROM db
WHERE user='graemed'
GROUP BY user, modified;
SELECT user, AVG(my_count), MIN(my_count), MAX(my_count)
FROM my_temp_table
GROUP BY user;
DROP my_temp_table;
HTH
Colin
