Sum Multiple Fields Not Adding Up

[kannuk]

Hey there - how are you? I'm trying to add up multiple fields from a table and it's mostly working but I have one curious error. It always seems to miss the field upgrade6_fee. I'm totally baffled. I thought it was my syntax but I'm not seeing anything wrong with it. Any ideas? Otherwise it all seems to be working.

 

<?php

$query = "SELECT fee1, upgrade3_fee, upgrade4_fee, upgrade5_fee, upgrade6_fee, upgrade7_fee, SUM(fee1 + upgrade3_fee + upgrade4_fee + upgrade5_fee + upgrade6_fee + upgrade7_fee) AS ttotal FROM Register";

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

 

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

echo number_format(($row['ttotal']), 2);

}

?>

[xyph]

I think you're using SUM incorrectly.

 

Since I don't know exactly what you're trying to accomplish, it's hared to provide you with a correct solution.

[kannuk]

I am trying to add up all of the entries from the above fields into one number. I've looked at other examples and this seems to work in theory. Like I said, when I have some of the other fields filled in, it adds up but upgrade6_fee always seems to be missing from the total. So if the grand total is supposed to be 10,000 and there is one entry for upgrade6_fee = 200, the grand total only shows up at 9,800.

 

My table has several hundred entries. The fields I used above are fees paid by the applicant. Fee1 is always filled out but the others may be 0. They are all decimals (00.0) in the table structure.

[xyph]

Perhaps a sample structure with data would help us solve your issue, along with what you expect the output to be.

 

As far as I can tell, you're selecting a bunch of data you don't use, but I don't know if you actually need that data.

[xyph]

After testing, that syntax should work fine.

 

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val1` int(11) NOT NULL,
  `val2` int(11) NOT NULL,
  `val3` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `val1`, `val2`, `val3`) VALUES
(1, 2, 4, 5),
(2, 1, 3, 9),
(3, 54, 2, 55),
(4, 34, 3, 4);

 

mysql> SELECT SUM(val1+val2+val3) as total_sum FROM items;
+-----------+
| total_sum |
+-----------+
|       176 |
+-----------+
1 row in set (0.00 sec)

 

Which is correct. I can't tell you why a value is missing, but it's probably user error, and not a problem with your SQL.

[kannuk]

Thanks for the reply. I've been testing different scenarios and the only error seems to be with the field upgrade6_fee. Not sure what to think - I've gone over it a dozen times!

[kannuk]

I figured it out. I was having a derp moment I guess.