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:

Code: Select all

num1   num2   num3
         9           5         7
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.