----- Original Message -----
From: "Eric Jensen" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Sent: Monday, May 02, 2005 11:19 AM
Subject: Re: Sum of a Count
> Rhino wrote:
>
> >I'm not entirely sure I know what you're asking when you say that you
want
> >to "group a field together with COUNT()".
> >
> >If you mean that you want to be able to count() and sum() or avg() the
> >groups, then the answer is yes. For example, let's say that you have an
> >Employee table with one row per employee. Each row also contains a
> >department number, indicating the department for which the employee
works.
> >In that case:
> >
> > select deptno, count(*), sum(salary), avg(bonus)
> > from Employee
> > group by deptno
> >
> >will return one row for each department number that exists in the table.
> >Each result set row will contain a department number, the number of
> >employees who belong to that department, the total salary paid out to all
> >employees in that department, and the average bonus paid out to all
> >employees in that department.
> >
> >If this isn't what you wanted, please clarify the question and someone
else
> >will probably try to answer the clarified question.
> >
> >Rhino
> >
> >----- Original Message -----
> >From: "Eric Jensen" <[EMAIL PROTECTED]>
> >To: <mysql@lists.mysql.com>
> >Sent: Friday, April 29, 2005 5:38 PM
> >Subject: Sum of a Count
> >
> >
> >
> >
> What I am wanting to do is run sum(), avg(), etc on what count()
> returns. So let's say I group a bunch of clients by a status field and
> then want to find the average number of clients per type. The logic
> would be something like SELECT status, AVG(COUNT(client_id)) FROM
> clients GROUP BY status; But that obviously doesn't work.
>
> Eric Jensen
>
I'm putting this back on the mailing list so that everyone can benefit from
this discussion. Other people may have better answers for your probleem....
It's not hard to do what you want if you don't mind using two statements. I
don't know of any way to do it in a single statement in MySQL, even in
Version 5, since a common table expression would be needed to do it in one
statement. I don't think common table expressions are supported even in
MySQL Version 5. (I hope someone will correct me if I'm wrong.)
I tested this simple example to verify that my solution would work:
---------------------------------------------
drop table if exists averages_temp;
create temporary table if not exists averages_temp
select workdept as deptno, count(*) as count
from Sample.Employee
group by workdept;
select avg(count)
from averages_temp;
---------------------------------------------
The approach I'm using is to get the counts for each group and store them in
a temporary table, then to calculate the average via a query to the
temporary table.
My example is using a table that contains one row for each employee in an
Employee table. Each row contains a department number in a column called
'workdept'. For my example, I want to know how many employees belong to each
department, then calculate the average number of employees in each
department.
This is how you can do the same work in a single statement (tested in DB2
V8.2); the query within the parentheses is called a common table expression
(and sometimes an 'inline view'):
---------------------------------------------------
select avg(count)
from (select workdept, count(*) as count
from employee
group by workdept) as e;
---------------------------------------------------
If you are running MySQL Version 5, you might want to give this a try to see
if it works in MySQL. (I'm still on Version 4.0.15 so I can't test it
myself.)
Rhino
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 29/04/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
