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:=@m+1 as Month,
count(*) AS Count,
@sum:=@sum+count(*) 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 <hchang@stripped> 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
