outputting sum of rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Anglophobe
Forum Newbie
Posts: 11
Joined: Tue May 09, 2006 11:50 am

outputting sum of rows

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Anglophobe
Forum Newbie
Posts: 11
Joined: Tue May 09, 2006 11:50 am

Post 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!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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";
?>
Anglophobe
Forum Newbie
Posts: 11
Joined: Tue May 09, 2006 11:50 am

Post by Anglophobe »

Wow, that's clean.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Yeah, i would do it like raghavan20 suggested...even cleaner ;)
Anglophobe
Forum Newbie
Posts: 11
Joined: Tue May 09, 2006 11:50 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Very nice.
Post Reply