Howto SUM GROUPS?

[Tracer]

I need a little SQL help Here (MySQL)...

TABLE
--user_id
--amount
--status

Basically I want to return a HTML table that will result in

user 1 1.00

user 1 5.25

______________total 6.25

user 2 100.00
user 2 101.25
user 2 135.21
_____________total 336.46

I know I can do this with multiple query's but can it be done with just one (I will not know the user_id's ?

[bastien]

why not just use a simple counter to add the totals for each user?

[Tracer]

I could.......

How would I seperate the tables and insert the total results in between each $user_id group?

[bastien]

<?
//db connection and query

$total_user=0;
$old_user="";

echo "<table>";

while ($rows=mysql_fetch_array($result)){

$user=$rows['user'];
$amount=$row['amount'];

if (old_user==$user){             
       [b]//if the name has not changed echo out the data[/b]
       echo "<tr><td>$user</td><td>$amount</td></tr>";

       [b]//add amount to the counter for the total[/b]
       $total_user+=$amount;      

}else{
       [b]//show the totals for the previous user[/b]
       echo "<tr><td>Total </td><td>$total_user</td></tr>";

       [b]//reset the counter[/b]
       $total_user=0;

       [b]//line separator for the different users[/b]
       echo "<tr><td colspan=2 height=20><hr></td></tr>";                 

       [b]//show data for the next user[/b]
       echo "<tr><td>$user</td><td>$amount</td></tr>";

}[b]//end if then statement[/b]

}[b]//end while loop[/b]

?>

hth

[Tracer]

Bastien....Im having trouble following the logic of your code....Here is how I have implemented it but it foes not appear to be working...

$old_user="";
$total_user=0;

//WHILE LOOP HERE		
while($row = mysql_fetch_array($results2, MYSQL_ASSOC))
{
	$status=$row['status'];
	$user=$row['rep'];
	$amount=$row['amount'];
	$date=$row['date'];
	$type=$row['type'];
	$description=$row['description'];
	$contact=$row['contact'];
	if ($status == "Submitted")
		{ $trbegin="<TR style=\"color:black; font-size:8pt; background-color:#66CCFF;\">"; }
	if ($status == "Approved")
		{ $trbegin="<TR style=\"color:black; font-size:8pt; background-color:#FFCC00;\">"; }

if ($old_user==$user)
	{
		//If the name has not changed echo out the data
		echo "$trbegin <TD>$user</TD><TD>$date</TD>
			<TD>$type</TD><TD>$description</TD><TD>$contact</TD>
			<TD>$amount</TD><TD>$row[status]</TD>
			<TD><INPUT type=checkbox name=approved[] value=$row[expense_id]</INPUT></TD>
			<TD><INPUT type=checkbox name=denied[] value=$row[expense_id]</INPUT>
    		</TD></TR>";	
		$total_user+=$amount;
	}
else 
	{
		if ($status == "Submitted")
			{ $trbegin="<TR style=\"color:black; font-size:8pt; background-color:#66CCFF;\">"; }
		if ($status == "Approved")
			{ $trbegin="<TR style=\"color:black; font-size:8pt; background-color:#FFCC00;\">"; }

		//show the totals for the previous user
		echo "<TR style=\"color:white; font-size:8pt; background-color:#003399;\">
			<TD colspan=9>Total:  $total_user</TD></TR>";	
		$total_user=0;
		echo "<TR><TD colspan=9 height=20>&nbsp</TD></TR>";
		echo "$trbegin <TD>$user</TD><TD>$date</TD>
			<TD>$type</TD><TD>$description</TD><TD>$contact</TD>
			<TD>$amount</TD><TD>$row[status]</TD>
			<TD><INPUT type=checkbox name=approved[] value=$row[expense_id]</INPUT></TD>
			<TD><INPUT type=checkbox name=denied[] value=$row[expense_id]</INPUT>
    		</TD></TR>";				
	}	

unset($status);
$count++;
}

Your help is truly appreciated!

[bastien]

what is the problem? makes it easier to debug if I know what I am looking since I don't have your db and duplicate the result quickly