List:General Discussion« Previous MessageNext Message »
From:Colin McKinnon Date:March 17 1999 10:52am
Subject:Re: Using avg, min, max functions on a non-numeric field.
View as plain text  
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

Thread
Using avg, min, max functions on a non-numeric field.Graeme B. Davis15 Mar
Re: Using avg, min, max functions on a non-numeric field.Graeme B. Davis16 Mar
  • Re: Using avg, min, max functions on a non-numeric field.Colin McKinnon17 Mar
Re: Using avg, min, max functions on a non-numeric field.Graeme B. Davis17 Mar