Hello.
It is possible to obtain your results without temporary tables, but
with user variables. For a pity you should execute three queries.
With first query you're getting the Sum:
select @all_sum:= count( employee_id) from table_employee
where employee_hire_date between "2005-01-01" and "2005-4-30";
With the second - number of month (this in most cases could be figured
out without query - with direct computation in next query, or on client
side):
select @all_num:= count( distinct month(employee_hire_date))
from table_employee
where employee_hire_date between "2005-01-01" and "2005-4-30";
And with the last query you should get what you want:
select employee_hire_date, count(employee_id), @all_sum as Sum,
@all_sum/@all_num as Avg
from table_employee
where employee_hire_date between "2005-01-01" and "2005-4-30"
group by month(employee_hire_date);
Henry Chang <[EMAIL PROTECTED]> wrote:
>
> Hello MySQL users,
>
> Currently, I use MySQL 4.0.22 and I can do a straightforward count of
> employees hired for each month.
>
> select employee_hire_date, count(employee_id)
> from table_employee
> where employee_hire_date between "2005-01-01" and "2005-4-30"
> group by month(employee_hire_date)
>
>
> Date | Count |
> --------------------
> 2005-01-01 | 123 |
> 2005-02-01 | 50 |
> 2005-03-01 | 76 |
> 2005-04-01 | 89 |
>
>
> However, I would like to do a grand total of the counts and the averages
> that would like the below.
>
>
> Date | Count | Sum | Avg |
> ---------------------------------
> 2005-01-01 | 123 | 338 | 84.5 |
> 2005-02-01 | 50 | 338 | 84.5 |
> 2005-03-01 | 76 | 338 | 84.5 |
> 2005-04-01 | 89 | 338 | 84.5 |
>
>
> Since my MySQL version is 4.0.22, I am not able to use subquery and I
> prefer not to use tmp tables. What would be the right query to solve
> for the grand total sum and average?? Any help would be greatly
> appreciated!!!
>
> Thanks in Advance.
>
> Henry
>
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
