Re: mixing GROUP BY, AVG and COUNT

2004-06-06 Thread Terry Riley 

--Original Message-  

> I have a table where the date a record was added is recorded in the 
> date column.  I can get count of how many records were entered on each 
> day by doing this
> SELECT COUNT(*)
> FROM table
> GROUP BY date;
> 
> I can get a total number of records by doing
> SELECT COUNT(*)
> FROM table
> 
> but how do I find the average count per day?  We can ignore the fact 
> that it is possible that no records are added on a given day.  I almost 
> for got this is on 4.0.18 so nested selects are not an option.
> 
> -- 
> Chris W


If I understood the question correctly, what you need is something like:

SELECT @totaldates:=COUNT(DISTINCT date) FROM Table;

SELECT COUNT(*)/@totaldates AS Average
FROM Table;

Regards
Terry Riley 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mixing GROUP BY, AVG and COUNT

2004-06-02 Thread Michael Stassen 
How about
  SELECT @total:=COUNT(*) FROM table;
  SELECT date, COUNT(*)/@total AS average
  FROM table GROUP BY date;
Michael
Chris W wrote:
I have a table where the date a record was added is recorded in the date 
column.  I can get count of how many records were entered on each day by 
doing this
SELECT COUNT(*)
FROM table
GROUP BY date;

I can get a total number of records by doing
SELECT COUNT(*)
FROM table
but how do I find the average count per day?  We can ignore the fact 
that it is possible that no records are added on a given day.  I almost 
for got this is on 4.0.18 so nested selects are not an option.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mixing GROUP BY, AVG and COUNT

2004-06-02 Thread Chris W 
I have a table where the date a record was added is recorded in the date 
column.  I can get count of how many records were entered on each day by 
doing this
SELECT COUNT(*)
FROM table
GROUP BY date;

I can get a total number of records by doing
SELECT COUNT(*)
FROM table
but how do I find the average count per day?  We can ignore the fact 
that it is possible that no records are added on a given day.  I almost 
for got this is on 4.0.18 so nested selects are not an option.

--
Chris W
Bring Back the HP 15C
http://hp15c.org
Not getting the gifts you want?  The Wish Zone can help.
http://thewishzone.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]