Sum of values from functions in query

[Lord_Alexandor]

I've been working on this stupid little code for about six hours, and I still cant get it to work. I don't even know how to do it. Here is what I have:
[FONT=courier new]
Tablename: Updates
+----+---------+-------+---------+------+--------------+
| id |  name   | catid | percent | desc | percenttotal |
+----+---------+-------+---------+------+--------------+
| 01 | update  |   1   |  50.00  | .... |    75.00     |
+----+---------+-------+---------+------+--------------+
| 02 | update2 |   1   |  75.00  | .... |    25.00     |
+----+---------+-------+---------+------+--------------+
| 03 | updateA |   2   |  25.00  | .... |    45.00     |
+----+---------+-------+---------+------+--------------+
| 04 | updateB |   2   |  50.00  | .... |    55.00     |
+----+---------+-------+---------+------+--------------+
[/FONT]
I need to do the following:

"((percent/100)*percenttotal)" for each id where the catid='1' and then add them all together.

I have no clue as to what I should do. I've tried many things, but they all failed. Please help me. >.<

[tsinka]

Hi,

Try this query ... I hope it works .... can't test it now:

$sql = "SELECT SUM((percent/100)*percenttotal) AS calc FROM Updates WHERE catid='1' GROUP BY catid";

$res = mysql_query($sql) or die("SQL error: ".mysql_error());

if (mysql_num_rows($res)) {
  $row = mysql_fetch_assoc($res);
  $calc = $row['calc'];
  // do something with the value
}

Thomas

[Lord_Alexandor]

:eek: wow...thanks! it works, but there are 2 extra zeros at the end after the decimal. If I can get those off, it would look awsome, but it doesn't make much difference. You rock. I have no idea how that works, but as long as it does, thats all that matters. I'll pick it apart later.

Thanks again! 😃

~Lord Alexandor

[tsinka]

Hi,

if you want to format a float you can use number_format, example:

$value="2.340232";
$value=number_format($value,2);

$value will be 2.34 afterwards.

Thomas

[TheDefender]

You have a few options for removing the 0s from the end...

1) [man]round()[/man] - This method will round the value up or down to the nearest whole number.

$sql = "SELECT SUM((percent/100)*percenttotal) AS calc FROM Updates WHERE catid='1' GROUP BY catid"; 

$res = mysql_query($sql) or die("SQL error: ".mysql_error()); 

if (mysql_num_rows($res)) { 
  $row = mysql_fetch_assoc($res); 
  $calc = round($row['calc'], 0); 
  // do something with the value 
}

2) [man]substr()[/man] - This method will chop off the last 3 items of the variable (The .00) and just give you the whole number.

$sql = "SELECT SUM((percent/100)*percenttotal) AS calc FROM Updates WHERE catid='1' GROUP BY catid"; 

$res = mysql_query($sql) or die("SQL error: ".mysql_error()); 

if (mysql_num_rows($res)) { 
  $row = mysql_fetch_assoc($res);
  $calc = substr($row['calc'], 0, (strlen($row['calc'])-3)); 
  // do something with the value 
}

You can also go with Tsinka's [man]number_format[/man] option. Thomas gave a good explanation and example of that too.

[Lord_Alexandor]

sorry for the late reply
I got kinda side tracked, but anywho...thanks for the help. I think I'll go with the round() method. It seems simple. Thank you both very much for helping me.