sum values in mysql column with comma

1
votes

I'm a php beginner and trying to make aggregation for specific columns in mysql db table but there is a comma separated values in this columns so the result not correct. this is a sample of table columns

column1

12,345.67

123,456.78

column2

12,345.67

123,456.78

column3

12,345.67

123,456.78

column4

12,345.67

123,456.78

please find my code below and Kindly advice if I'll use a solution how I can use it.

<?php 
$query=mysql_query("select * from mytable")or die(mysql_error());
while($row=mysql_fetch_array($query)){
?>
<tr>
<td><?php echo $row['column1'] ?></td>
<td><?php echo $row['column2'] ?></td>
<td><?php echo $row['column3'] ?></td>
<td><?php echo $row['column4'] ?></td>
</tr>
<?php } ?>
</tbody>
</table>
<?php
$result = mysql_query("SELECT sum(column1 + column2 + column3 + column4) FROM mytable") or die(mysql_error());
while ($rows = mysql_fetch_array($result)) {
?>
<div>
&nbsp;Total:&nbsp;<?php echo $rows['sum(column1 + column2 + column3 + column4)']; ?></div>
<?php }
?>
<?php }
?>
4
phpmysqldatabase
You'll need to parse the string to a double in PHP and do the summing there, MYSQL won't sum it with commas i don't think. Can I ask why you're using a string in a database to represent a numeric value? Use your code for formatting, store your data properly in it's correct type. - nospi
I did not create the database and I know that the data should be stored properly but this is a kind of test. could you please explain your solution on my code so I can understand it. - Triple M

4 Answers

1
votes

For mysql to properly run aggregations you need to specify each field that will be summed rather than use SELECT *.

For example <?php $query=mysql_query("select Order_Name, SUM(ORDER_VALUE), SUM(ORDER_COUNT) SUM(ORDER_TAX+ORDER_SHIPPING AS 'fees') from mytable...

If the aggregations aren't working because based on the presence of a comma in your CSV source data then you probably need to set your database field to be the correct type, which is probably going to be FLOAT or DOUBLE if you only want 2 decimal places.

1
votes

this will add some value with comma:

set @num1 = '222,3039';
set @num2 = '23,444990';
select REPLACE(@num1,',','')+ REPLACE(@num2,',','')
0
votes

Firstly I'd highly recommend using the correct data types in your database, but as you've indicated this is not possible, the easiest way is to parse the string to a double value in your existing loop, and keep a running total.

$query=mysql_query("select * from mytable")or die(mysql_error());
while($row=mysql_fetch_array($query)){
    ... existing code ...

    #  remove the comma, and cast the string to a double
    $col1 = (double) str_replace(",", "", $row->column1);
    ... $col2, $col3 ...
    $col4 = (double) str_replace(",", "", $row->column4);

    $rowVal = $col1 + $col2 + $col3 + $col4;

    #  keep a running total
    $totalVal += $rowVal;
}

Then when you need it later on

#  format the total again as you see fit
echo $totalVal;                             // straight double
echo number_format($totalVal, 2, '.', ','); // thousand separator and decimal point

Recommended

Clean up your database structure so you can use properly formatted mysql. Also, these functions (mysql_query) and the like are deprecated. Look into PDO, prepared statements.

http://php.net/manual/en/book.pdo.php - good one for configuration / reference https://phpdelusions.net/pdo - self-advertised as the only proper guide, so surely worth a look

0
votes

SQL doesn't assign sums that way.

change the select 

SELECT sum(column1 + column2 + column3 + column4) FROM mytable

to

SELECT sum(column1 + column2 + column3 + column4) AS MYSUM FROM mytable

and echo $row['MYSUM'];