Help Needed.... Selective summation from row

[alanchai]

Hi there,

I am a beginner in PHP and MySQL. Trying out a simple query and I am stucked. Need expert helps.

Thanks in advance.

Regards,
Alan

+++++++++++++++++++++++++++++++++++++++++++++++

I have a simple database like the following stored in MySQL

============================================
Month _______Responsetime ___Resolutiontime
============================================
1 _____________ 0.5 ___________ 6.2
1 _____________ 1.5 ___________ 19.5
3 _____________ 2.5 ___________ 12.2
2 _____________ 1.5 ___________ 6.4
2 _____________ 0.8 ___________ 5.3
1 _____________ 2.5 ___________ 8.7

I am trying to write a PHP script to query MySQL and get the average for Responsetime and Resolutiontime.

I manage to get the overall average but not able to get the monthly average.

Trying to achieve the following on the screen:

============================================
_______________Average_________Average______
Month _______Responsetime ___Resolutiontime
============================================
1 _____________ 1.5 ___________ 11.47
2 _____________ 1.15 ___________5.85
3 _____________ 2.5 ___________ 12.2



How can I script it to break down the average Responsetime and Resolutiontime for each month?


My code is as follows:
======================

php:

<?php

$link = mysql_connect("localhost","root","password")
or die(mysql_error());

mysql_select_db("test")
or die (mysql_error());

$queue = 'Queue999';

$query = "SELECT TicketID, Ticket_No, Region, " .
"Resolution_Time_hr, Response_Time_hr, Queue, Response_Time_period, Resolution_Time_period " .
"FROM raw_data " .
"WHERE Queue='$queue'";

$result = mysql_query($query, $link)
or die(mysql_error());

$num_tickets = mysql_num_rows($result);


$ticket_header=<<<EOD
<h2><center>Database Query</center></h2>
<h3><center>Queue: $queue <br>Total: $num_tickets</center></h3>
<table width="70%" border="1" cellpadding="2"
cellspacing="2" align="center">
<tr>
<td><b>Ticket No</b></td>
<td><b>Resolution Time (Hours)</b></td>
<td><b>Response Tme (Hours)</b></td>
<td><b>Resolution Tme Period</b></td>
<td><b>Response Tme Period</b></td>
<td><b>Queue</b></td>
</tr>
EOD;
echo $ticket_header;

$ticket_details ='';

$sum_rsth = '';
$sum_rpth = '';

while ($row = mysql_fetch_array($result))
{
$ticket_no = $row['Ticket_No'];
$rsth = $row['Resolution_Time_hr'];
$rpth = $row['Response_Time_hr'];
$rqueue = $row['Queue'];
$rst = $row['Resolution_Time_Period'];
$rpt = $row['Response_Time_Period'];


$sum_rsth = $sum_rsth + $rsth;
$sum_rpth = $sum_rpth + $rpth;

$ticket_details .=<<<EOD
<tr>
<td>$ticket_no</td>
<td>$rsth</td>
<td>$rpth</td>
<td>$rst</td>
<td>$rpt</td>
<td>$rqueue</td>
</tr>
EOD;
}

$ave_rpth = $sum_rpth / $num_tickets;
$ave_rsth = $sum_rsth / $num_tickets;

$ticket_details .=<<<EOD
<tr>
<td><b>Average:</b> </td>
<td><b>$ave_rsth hours</b> </td>
<td><b>$ave_rpth hours</b></td>
<td>&nbsp;</td>
<td><b>Total:</b></td>
<td><b>$num_tickets</b></td>
</tr>
EOD;
echo $ticket_details;
echo "</TABLE>";

?>