...

View Full Version : ratio of row1 and row 2

I3arry
07-02-2007, 04:32 PM
I have a row called totalkills and one called totaldeaths, how could i have it return the ratio of them both EG totalkills = 100 totaldeaths = 10 ratio = 10/1

(not sure if php or sql forum is best!)

ralph l mayo
07-03-2007, 01:48 PM
Is this a trick question or something? SELECT totalkills/totaldeaths FROM table

I3arry
07-03-2007, 03:37 PM
...

and how does that helpget the ratio?

whizard
07-03-2007, 03:59 PM
That is the query you use, and it will return the ratio you are looking for.

Dan

ralph l mayo
07-04-2007, 02:34 AM
If you're aiming to get fractions from the table you can use this:

DELIMITER ~~

CREATE FUNCTION normalized_fraction(a int, b int) RETURNS text
LANGUAGE SQL
DETERMINISTIC
NO SQL
BEGIN
DECLARE tmp, orig_a, orig_b int;

IF b = 0 THEN
RETURN NULL;
END IF;

SET orig_a = a;
SET orig_b = b;

REPEAT
SET tmp = b;
SET b = a % b;
SET a = tmp;
UNTIL b = 0
END REPEAT;

SET orig_a = orig_a / a;
SET orig_b = orig_b / a;
RETURN CONCAT(orig_a, '/', orig_b);
END~~

After which:

mysql> SELECT *, totalkills/totaldeaths AS ratio, normalized_fraction(totalkills, totaldeaths) AS fractional_ratio FROM test;
+------------+-------------+----------+------------------+
| totalkills | totaldeaths | ratio | fractional_ratio |
+------------+-------------+----------+------------------+
| 1000 | 10 | 100.0000 | 100/1 |
| 10 | 1000 | 0.0100 | 1/100 |
| 10 | 0 | NULL | NULL |
| 0 | 10 | 0.0000 | 0/1 |
+------------+-------------+----------+------------------+
4 rows in set (0.00 sec)

I3arry
07-04-2007, 05:34 PM
ok, (totalkills/totaldeaths) as totalkd works

How about doing the math when entering the data

I tried

\$totalkd = trim(\$playersArray[\$i][2])/trim(\$playersArray[\$i][3]);

and

\$totalkd = \$totalkills/\$totaldeaths;

And they both return single numbers

edit fixed, forgot to set it to float