CODEWITHSUNDEEP
phpmysqldatabase
Triple M
Triple M

Reputation: 39

sum values in mysql column with comma

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 }
?>

Upvotes: 1

Views: 2896

Answers (4)

nospi
nospi

Reputation: 121

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

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

this will add some value with comma:

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

Upvotes: 1

Chris Reilly
Chris Reilly

Reputation: 11

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.

Upvotes: 1

Forbs
Forbs

Reputation: 1276

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'];

Upvotes: 0

Related Questions