Page 1 of 1
outputting sum of rows
Posted: Mon May 15, 2006 10:03 am
by Anglophobe
How would I get php to output the sum of all the entries into a column, gathered from a recordset?
such that table:
Code: Select all
id num1 num2 num3
1 3 2 4
2 2 2 3
3 4 1 0
would output:
I'm using LAMP(remote) or WAMP(development), developing with DreamWeaver.
Posted: Mon May 15, 2006 11:20 pm
by RobertGonzalez
Posted: Tue May 16, 2006 12:00 am
by Anglophobe
Not really what I needed - I needed something to add down a column, not across a row, but someone on another board did give me a solution. Here it is, in case anyone else can benefit:
Code: Select all
<?php $i=0;
do {
$i++; ?>
<tr><td><?php echo $row_rsRecordSet['col1']; ?></td>
<td><?php echo $row_rsRecordSet['col2']; ?></td>
<td><?php echo $row_rsRecordSet['col3']; ?></td>
<td>Subtotal=<?php $sums[$i]=array_sum($row_rsRecordSet['col1'],$row_rsRecordSet['col2'],$row_rsRecordSet['col3']); echo $sums[$i]; ?></td></tr>
<?php } while ($row_rsRecordSet = mysql_fetch_assoc($rsRecordSet)); ?>
<tr><td>Grand Total: <?php echo array_sum($sums); ?></td></tr>
Should do something like:
3 2 4 Subtotal=9
2 2 3 Subtotal=7
4 1 0 Subtotal=5
Grand Total: 21
You could also easily adapt it to just add the total for each row. The $i variable is the key, but I never would've thought of it on my own...yay!
Posted: Tue May 16, 2006 12:06 am
by RobertGonzalez
For that you could also do...
Code: Select all
<?php
$col1 = 0;
$col2 = 0;
$col3 = 0;
while ($row = mysql_fetch_array($result))
{
$col1 += $row['col1'];
$col2 += $row['col2'];
$col3 += $row['col3'];
}
$grandtotal = $col1 + $col2 + $col3;
echo "Col 1 total is $col1, Col 2 total is $col2, Col 3 total is $col3.<br />\n";
echo "The grand total is $grandtotal.<br />\n";
?>
Posted: Tue May 16, 2006 12:47 am
by Anglophobe
Wow, that's clean.
Posted: Tue May 16, 2006 2:15 am
by raghavan20
Code: Select all
mysql> create table sumNumbers(
-> n1 int,
-> n2 int,
-> n3 int);
Query OK, 0 rows affected (0.48 sec)
mysql> insert into sumNumbers values (3, 2, 4), (2, 2, 3), (4, 1, 0);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select sum(n1), sum(n2), sum(n3) from sumNumbers;
+---------+---------+---------+
| sum(n1) | sum(n2) | sum(n3) |
+---------+---------+---------+
| 9 | 5 | 7 |
+---------+---------+---------+
1 row in set (0.00 sec)
Posted: Tue May 16, 2006 5:56 am
by JayBird
Yeah, i would do it like raghavan20 suggested...even cleaner

Posted: Tue May 16, 2006 9:30 am
by Anglophobe
Can you use that in conjunction with WHERE? Wait, let me try
Code: Select all
mysql> create table sumnumbers(
-> n1 int,
-> n2 int,
-> n3 int);
Query OK, 0 rows affected (0.20 sec)
mysql> insert into sumnumbers values (3,2,4), (2,2,3), (4,1,0);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select sum(n1), sum(n2), sum(n3) from sumnumbers WHERE n3 != 0;
+---------+---------+---------+
| sum(n1) | sum(n2) | sum(n3) |
+---------+---------+---------+
| 5 | 4 | 7 |
+---------+---------+---------+
1 row in set (0.03 sec)
Kewl.
Posted: Tue May 16, 2006 11:00 am
by RobertGonzalez
Very nice.