From: Colin McKinnon
Date: March 17 1999 10:52am
Subject: Re: Using avg, min, max functions on a non-numeric field.
List-Archive: http://lists.mysql.com/mysql/405
Message-Id: <3.0.5.32.19990317105238.0082be50@lonmay>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

At 09:44 16/03/99 -0500, "Graeme B. Davis" <GraemeD@stripped> wrote:
>Does anyone have any insight on this?=A0 (I tried selecting avg(count(user)=
 but
>that didn't work :( )
>
>select user, modified, count(user) as count from db where user=3D'graemed'
group
>by modified;
>
>+--------+------------+------------+
>| count=A0 | user=A0=A0=A0=A0=A0=A0 | modified=A0=A0 |
>+--------+------------+------------+
>|=A0=A0=A0=A0 20 | graemed=A0=A0=A0 | 1999-02-01 |
>|=A0=A0=A0=A0 17 | graemed=A0=A0=A0 | 1999-02-02 |
>|=A0=A0=A0=A0=A0 5 | graemed=A0=A0=A0 | 1999-02-03 |
>|=A0=A0=A0=A0 24 | graemed=A0=A0=A0 | 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=20
INTO my_temp_table
FROM db=20
WHERE user=3D'graemed'=20
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