From: Graeme B. Davis
Date: March 17 1999 2:46pm
Subject: Re: Using avg, min, max functions on a non-numeric field.
List-Archive: http://lists.mysql.com/mysql/423
Message-Id: <01be7085$03394a00$9927253f@norway.corp.us.uu.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

Hi Colin,

Yeah, it turned out that I had to put the count into a temp table and then do
the min,max,avg on that table.... works fine now.  :)

Thanks for your help!

-graeme


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


---------------------------------------------------------------------
To request this thread, e-mail mysql-thread405@stripped
To unsubscribe, e-mail the address shown in the
List-Unsubscribe header of this message.
For additional commands, e-mail: mysql-help@stripped