function SQL SUM query

Question

I have a table with numbers like this:

 ______________________________________
| axle_id | train_id | axle | distance |  
|_________|__________|______|__________|   
|   1     |     1    |   1  |    20    |
|   2     |     1    |   2  |    50    |
|   3     |     1    |   3  |   200    |
|   4     |     1    |   4  |    50    |
|   5     |     1    |   5  |   200    |
|   6     |     1    |   6  |    50    |
|   7     |     1    |   7  |   200    |
|   8     |     1    |   8  |    50    |
|_________|__________|______|__________|

Now i want to count them and make a calculation with SUM. The code i have right now:

function count_axles($id) {
        $sql = "SELECT sum(distance)/(25000)*(100) as totaldistance from axle WHERE train_id = :train_id";
        $sth = $this->pdo->prepare($sql);
        $sth->bindParam(":train_id", $id, PDO::PARAM_STR);
        $sth->execute();
        return $sth->fetchAll();
    }

And i call this function via:

<?php
        $count_axle = $database->count_axles($_GET['train_id']);
?>
<?php      
    foreach($count_axle as $counting){ 
    }

        echo $counting['totaldistance'];
?>

Now the output is correct.
(3.2800000000000002) This is the percentage of 25.000
But i want to add 5000 like:

$sql = "SELECT sum(distance)+(5000)/(25000)*(100) as totaldistance from axle WHERE train_id = :train_id";

But when i do that, the output generates something randomly like: 840

Why does it do this, and how do i solve this?

Probably getting hosed by integer division.

duffymo
– duffymo

2015-04-21 14:19:09 +00:00
Commented Apr 21, 2015 at 14:19 — duffymo
– duffymo, Commented Apr 21, 2015 at 14:19
Try putting (sum(distance)+(5000)) instead.

yergo
– yergo

2015-04-21 14:19:28 +00:00
Commented Apr 21, 2015 at 14:19 — yergo
– yergo, Commented Apr 21, 2015 at 14:19

Gordon Linoff · Accepted Answer · 2015-04-21 15:12:19Z

6

Basic math. You're doing

                       5000
  sum(distance) + --------------
                     25000 * 100

when you really want

  sum(distance)  + 5000
  ------------------------
        25000 * 100

Try

SELECT (sum(distance)+(5000))/(25000)*(100)
       ^--------------------^

instead. Note the extra brackets.

And remember the old BEDMAS mnemonic: brackets, exponents, division, multiplication, addition, subtraction...

edited Apr 21, 2015 at 15:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

answered Apr 21, 2015 at 14:20

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abhik Chakraborty Over a year ago

And from school days we all perhaps learned about BODMAS mathsisfun.com/operation-order-bodmas.html

Mitch Over a year ago

It is working! Thank you very much! And i can accept answer in 9 minuts

Juan · Accepted Answer · 2015-04-21 14:22:35Z

1

Try with:

$sql = "SELECT (sum(distance)+(5000))/(25000)*(100) as totaldistance from axle WHERE train_id = :train_id";

Division has preference over sum operations.

answered Apr 21, 2015 at 14:22

Juan

3,70323 silver badges34 bronze badges

Collectives™ on Stack Overflow

function SQL SUM query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related