Jump to content


Photo

How do I add the sum of this result?


  • Please log in to reply
6 replies to this topic

#1 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 16 August 2006 - 03:15 PM

while ($RESULT = mssql_fetch_assoc($RESULTDS))  {

echo "<tr align=center>";
echo "<td>";

//echo $combineddate;

echo $RESULT['Lmo']; 
echo "/";
echo $RESULT['Lda'];
echo "/";
echo $RESULT['LYR'];

echo "</td>";

echo "<td>";
echo $RESULT['Leave Code'];
echo "</td>";

echo "<td>";
echo $RESULT['Hours'];
echo "</td>";

echo "</tr>";
}

//printf ("%01.2f", $TotalHours);

print("</table>");

?>

This produces the total hours of leave taken over a period of time. What would be the right way to add these hours? When I try the usual SUM statement, it gives me the figure for one record only. I've also tried to include the SUM statement within the WHILE statement and that pretty much hoses the entire thing.

Anybody?



#2 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 16 August 2006 - 03:17 PM

Can we see your sql statement Moron?

#3 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 16 August 2006 - 03:30 PM

Can we see your sql statement Moron?


Here you go:

$RESULTDS=mssql_query("SELECT DISTINCT LH.[Employee Number], LH.[Lmo], LH.[Lda], LH.[LYR], LH.[Hours], LH.[Leave Code], M2.[HRYRAT], M2.[EMPNO], M2.[MANLAP], M2.[MANLAC], M2.[MANLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[NAMEMI], M2.[NAMEL], M2.[NAMEF] FROM LEAVHST LH INNER JOIN MASTERL2 M2 ON LH.[Employee Number]=M2.EMPNO WHERE M2.[EMPNO] = '".$_POST['employeenumber']."'    ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc");
$RESULT=mssql_fetch_assoc($RESULTDS);




#4 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 16 August 2006 - 03:33 PM

Explain exactly what it is you want to get the sum of. Your not really helping much here. A simple example would be...

SELECT SUM(hours) AS totalhours FROM tbl


#5 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 16 August 2006 - 03:40 PM

Explain exactly what it is you want to get the sum of. Your not really helping much here. A simple example would be...

SELECT SUM(hours) AS totalhours FROM tbl


I see. This part of the code...

echo "<td>";
echo $RESULT['Hours']; 
echo "</td>";


.... pulls the hours of leave taken for each record. What I want to do is add the hours from each record displayed.

I've tried doing it like in your example above but I can't seem to properly integrate that statement into my query.

If there is a way to do this outside of the query? If so, it would be my preferred method.



#6 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 16 August 2006 - 03:56 PM

<?php
$total_hours = 0;//assign 0 to it otherwise php5 will give a warning
while ($RESULT = mssql_fetch_assoc($RESULTDS))  {

echo "<tr align=center>";
echo "<td>";

//echo $combineddate;

echo $RESULT['Lmo'];  
echo "/";
echo $RESULT['Lda'];
echo "/";
echo $RESULT['LYR']; 

echo "</td>";

echo "<td>";
echo $RESULT['Leave Code'];
echo "</td>";

echo "<td>";
$total_hours = $total_hours + $RESULT['Hours'];
echo $RESULT['Hours'];   
echo "</td>";

echo "</tr>";
}

//printf ("%01.2f", $TotalHours);

print("</table>");
echo "Total hours: $total_hours";
?>


#7 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 16 August 2006 - 04:08 PM

<?php
$total_hours = 0;//assign 0 to it otherwise php5 will give a warning
while ($RESULT = mssql_fetch_assoc($RESULTDS))  {

echo "<tr align=center>";
echo "<td>";

//echo $combineddate;

echo $RESULT['Lmo'];  
echo "/";
echo $RESULT['Lda'];
echo "/";
echo $RESULT['LYR']; 

echo "</td>";

echo "<td>";
echo $RESULT['Leave Code'];
echo "</td>";

echo "<td>";
$total_hours = $total_hours + $RESULT['Hours'];
echo $RESULT['Hours'];   
echo "</td>";

echo "</tr>";
}

//printf ("%01.2f", $TotalHours);

print("</table>");
echo "Total hours: $total_hours";
?>


Thank you, thank you, thank you!

Ginger, you ROCK!

:)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users