Totalizing a Mysql Table

[lexer]

Hi

I've a Mysql table with the following fields Ext, Elapse and Cost:

Ext Elapse Cost
236 5 150
206 1 30
222 5 150
220 2 60
223 3 90
236 2 60
236 2 60
210 4 120
223 3 90

I need to totalize the Elapse and Cost according to the field Ext, but I need to see every register for Ext, Something like this:

Ext Elapse Cost
206 1 30
Total 1 30

210 4 120
Total 4 120

220 2 60
Total 2 60

222 5 150
Total 5 150

223 3 90
223 3 90
Total 6 180

236 5 150
236 2 60
236 2 60
Total 9 270

Using GROUP BY I can totalize the fields Elapse and Cost according to Ext but I can't see every register for Ext:


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("calls", $con);

// Construct our join query

$query = "SELECT Ext, SUM(elapse) AS TotalElapse, SUM(cost) AS TotalCost ".
"FROM register ".
"GROUP BY Ext ORDER BY Ext";



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



// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['Ext']. " - ". $row[' TotalElapse ']. " - ". $row[' TotalCost '];
echo "<br />";
}
?>



This is the output from the above program:

Ext TotalElapse TotalCost
206 1 30
210 4 120
220 2 60
222 8 150
223 6 180
236 9 270

Any Idea, Please?

 

[r937]

ideas? try this --

SELECT Ext, Elapse, SUM(cost) AS TotalCost
FROM register
GROUP
BY Ext, Elapse WITH ROLLUP
ORDER
BY Ext

r937.com | rudy.ca

 

[lexer]

r937, Thanks for your answer, Using your code I already could totalize the cost by Ext Field.

this the code:


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("calls", $con);

// Construct query
$query = "SELECT Ext, SUM(costo) AS Costototal ".
"FROM registro ".
"GROUP BY Ext, costo WITH ROLLUP";

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

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['Ext']. " - ". $row[' TotalElapse ']. " - ". $row[' TotalCost '];
echo "<br />";
}
?>




This is the output from the above program:

Ext Elapse TotalCost
206 1 30
206 1 30
210 2 60
210 4 120
210 4 180
220 2 60
220 2 60
222 5 150
222 5 150
223 3 90
223 3 90
236 2 60
236 3 90
236 5 150
236 5 300
5 810

I would like to insert the word "Total", For every Ext field totalized, Like This:

Ext Elapse TotalCost
206 1 30
Total 30
210 2 60
210 4 120
Total 180
220 2 60
Total 60
222 5 150
Total 150
223 3 90
Total 90
236 2 60
236 3 90
236 5 150
Total 300


Any Ideas, Please?

 

[r937]

sorry, i don't do php (this is the mysql forum)

by the way, that output doesn't match that query



r937.com | rudy.ca