PHP SUM function

Question

I have a table like following:

id    q_id         value  
------------------------
1     2            5 
2     2            NULL 
3     2            5 
4     2            NULL 
5     4            2 
6     4            NULL 
7     4            2 
8     4            NULL

What I want is to get the sum of (for example) all value where q_id = 2

    $sq = mysql_query("SELECT SUM(value) AS sum FROM  table WHERE q_id = 2)or die(mysql_error());
   while($row = mysql_fetch_array($sq)){
    $sum = $row['sum'];
     }
  echo $sum."<br>";

But I'm getting

5
5

But what I want is the sum of the value and expecting 10 instead.

Thank you for helping.

Please stop using mysql_* functions as they are in the deprecation process. Be a better PHP Developer. — Jason McCreary
– Jason McCreary, Commented Sep 4, 2012 at 21:31
The code you posted contains syntax errors, and the query you showed will not produce the result you indicated. I don't think you're actually doing what you're showing us. — Gavin Towey
– Gavin Towey, Commented Sep 4, 2012 at 21:32
@Dagon: Not required here, as is filtered by q_id in the WHERE clause and MySQL will group all records together if an aggregate function is used without GROUP BY. — eggyal
– eggyal, Commented Sep 4, 2012 at 21:33
@eggyal golly, i thought it was compulsory for all the aggregate functions, thanks for the info — user557846
– user557846, Commented Sep 4, 2012 at 21:34

itsmequinn · Accepted Answer · 2012-09-04 23:21:08Z

3

If you're going to loop over the result set anyway, why not just use

SELECT value FROM table WHERE q_id=2

then sum up those values using the while loop? Something like:

while($row = mysql_fetch_array($sq)) { $sum += $row['value']; } echo $sum."<br>";

Edit: also, as Jason McCreary said above, you should look into an alternate method of querying the database. I would suggest searching php.net for "PDO", which is very easy to use.

answered Sep 4, 2012 at 23:21

itsmequinn

1,0841 gold badge9 silver badges23 bronze badges

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

Comments

Amit Prajapati · Accepted Answer · 2020-12-05 15:11:44Z

1

We can directly sum in the query like

SELECT 5+6 AS addition

answered Dec 5, 2020 at 15:11

Amit Prajapati

1371 silver badge6 bronze badges

Comments

Mayura · Accepted Answer · 2012-09-04 21:49:02Z

0

Give it a try... You are displaying value, there was missing quote in your code.

   $sq = mysql_query("SELECT SUM(value) AS sum FROM  `table` WHERE `q_id` = '2'")or die(mysql_error());

   while($row = mysql_fetch_assoc($sq))
   {
     $sum = $row["sum"];
   }

   echo $sum . "<br>";

edited Sep 4, 2012 at 21:49

answered Sep 4, 2012 at 21:33

Mayura

1,9791 gold badge20 silver badges22 bronze badges

Comments

user7182340 · Accepted Answer · 2017-06-17 13:22:19Z

0

 $sq="SELECT  value FROM table WHERE q_id='".$am."'"; 
 $result=mysqli_query($link,$sq); 
while($row=mysqli_fetch_assoc($result)) { 
 $sum += $row['value']; 
     } 
 echo "<p>Sum:&nbsp;".$sum."</p><br>";
 //$am ='2'; 
 //$link  -- connection to the data base

Please put $am ='2'; before the select statement and also make sure you have connected to the data base using $link

You will get the total sum according to the value of q_id

I have tested the code and works fine.

answered Jun 17, 2017 at 13:22

user7182340

Comments

Mike · Accepted Answer · 2012-09-04 21:32:18Z

-1

You need to add a GROUP BY to your query. Add the following to the end

GROUP BY q_id

answered Sep 4, 2012 at 21:32

Mike

1,81114 silver badges14 bronze badges

5 Comments

Viktor S. Over a year ago

Maybe - remove group by? He already gets result like it has a group by and want an opposite result

eggyal Over a year ago

This is not needed on MySQL in the presence of WHERE q_id = 2.

user557846 Over a year ago

does any one read the comments before posting their answer ? :-)

eggyal Over a year ago

@Dagon: In fairness, this was posted around the same time as your original comment.

Jonas Willander Over a year ago

Thank you all guys but still the same problem even with GROUP BY q_id.

Collectives™ on Stack Overflow

PHP SUM function

5 Answers 5

Comments

Comments

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related