Re: Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)
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]
Re: Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)
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]
Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)
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]
