ISSUE: Mysql converting Enum to Int

user_800619 0
Viewed 18143

I have a very simple rating system in my database where each rating is stored as an enum('1','-1'). To calculate the total I tried using this statement:

SELECT SUM(CONVERT(rating, SIGNED)) as value from table WHERE _id = 1

This works fine for the positive 1 but for some reason the -1 are parsed out to 2's.

Can anyone help or offer incite?

Or should I give up and just change the column to a SIGNED INT(1)?

enums int mysql
6 Answers

I wouldn't use enum here too, but it is still possible in this case to get what is needed

Creating table:

CREATE TABLE test (
    _id INT PRIMARY KEY,
    rating ENUM('1', '-1')
);

Filling table:

INSERT INTO test VALUES(1, "1"), (2, "1"), (3, "-1"), (4, "-1"), (5, "-1");

Performing math operations on enums converts them to indexes, so it is possible just to scale the result value:

SELECT 
    SUM(3 - rating * 2)
FROM
    test;

Result: -1 which is true for the test case.

user_17679120
Related