Without knowing more of your requirements, I find seeing the grand total and
overall average repeated in every row strange from a usability perspective.
Do you really need that, or were you just hoping to get that information out
of your query?
I'd suggest something like:
SET @sum=0, @m=0;
SELECT MONTH(employee_hire_date) AS date,
@m:[EMAIL PROTECTED] as Month,
count(*) AS Count,
@sum:[EMAIL PROTECTED](*) AS Sum,
ROUND((@sum+count(*))/(@m),1) AS Avg
FROM table_employee
WHERE employee_hire_date BETWEEN '2005-01-01' AND '2005-4-30'
GROUP BY MONTH(employee_hire_date);
Date | Month | Count | Sum | Avg |
--------+-------+-------+-----+-------+
2005-01 | 1 | 123 | 123 | 123.0 |
2005-02 | 2 | 50 | 173 | 86.5 |
2005-03 | 3 | 76 | 249 | 83.0 |
2005-04 | 4 | 89 | 338 | 84.5 |
The Sum column is a running total, and the Avg column is the average "so far".
Hence, the grand total and overall average are in the last row. Would that do?
Michael
Gleb Paharenko wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
