Cannot subtract one value from another (unsigned)

integer, mysql, sql, unsigned

Solution

Unsigned remain unsigned, so you have a problem when the result would be negative. Cast to signed before the subtraction:

SELECT id, up, down, cast(up as signed) - cast(down as signed) AS result
FROM stats
ORDER BY result DESC
LIMIT 0, 10;

Or, keep your query and add a `where` clause:

SELECT id, up, down, (up-down) AS result
FROM stats
WHERE up >= down
ORDER BY result DESC
LIMIT 0,10;

Problem

I have a table `stats` with three columns: - `id` - `up` - number of upvotes (just like here at StackOverflow) - `down` - analogue to `up` `up` and `down` are `INT(11)` and `UNSIGNED` (because they'll only be positive values). Now when I want to fetch the ten items with the highest `(up-down)` value, I'm using this query: ``` SELECT id, up, down, (up-down) AS result FROM stats ORDER BY result DESC LIMIT 0,10 ``` But I'm getting an error ``` #1690 - BIGINT UNSIGNED value is out of range in '(`database`.`stats`.`up` - `database`.`stats`.`down`)' ``` If I leave out the `ORDER BY result DESC` everything runs smoothly (except for the fact that they're not ordered by `result`, but the math of `up-down` is working). What would I have to change in my query in order to retreive the correct result? Or do I have to remove the `UNSIGNED` attribute? But isn't this an appropriate case where I should use that attribute?

Original source