[RESOLVED] Calculating totals by month and year

[mlosier]

Hi,

I am tryng to create a report so i can show a report of the usage of a group of users by month and year. i cant figure out a way to make it work.

Anybody have an idea how to do this?

here is what i have for code right now

$sql_group_members=mysql_query("select username from radius_reports.group_usernames where group_id = $group_id") or die(mysql_error());

				while($row_group_members=mysql_fetch_array($sql_group_members))
				{
					$username = $row_group_members['username'];
					$sql_usage=mysql_query("select (inputoctets + outputoctets)/1048576 as mb, Year(acctdate) as year, MONTH(acctdate) as month from radius.totacct where acctdate between '$start_date' and '$end_date' and username ='$username' group by Year(acctdate),Month(acctdate)") or die(mysql_error());
					while($row_usage=mysql_fetch_array($sql_usage))
					{					
						echo "<td>".$row_usage['year']."";
						echo "<td>".$row_usage['month']."";
						echo "<td>".$row_usage['mb']."";
						echo "<tr><td>";

					}

				}

here is the output:
2009 2 17.8569
2009 3 33.9679
2009 1 6.5958
2009 2 13.2488
2009 3 46.6239
2009 3 1.0769
2009 3 0.2248

i want the months to be grouped and the totals to be summed

thanks

[scrupul0us]

the easiest way todo this honestly is loop through your sql statement 12 times and have mysql calculate the totals for you (each time only selecting records for each month)... just add a sum(mb) as totalMB or equiv for your total usage

if you want to do it with PHP on one query, you'll have to sort by month in addition to year and then do a look ahead on the next row to see if the month is or isnt the same (at the same time using a variable to += the value to you are summing)... if the month ISNT the same, dump the total value and reset the summing variable back to 0

in general im a fan of having MYSQL do work for me

[mlosier]

How do I do a look ahead on the next row?

[mlosier]

grrr i cant figure how to do this...... god help me !!

[mlosier]

I did it!!

Yeah!

Thank You