CODEWITHSUNDEEP
mysqlsql
Freeman
Freeman

Reputation: 1190

Using SUM() on ENUM column with negative numbers

I want to restrict the value in the column "voteValue" to either -1 or 1, so I used an ENUM data type for this. However, when I try to calculate the total value of the votes (that is, +1 votes and -1 votes), I end up getting +2 for each -1 vote instead of subtraction.

I know I can just divide this to 2 different SQL codes to get the SUMS of +1 and -1 votes separately, but I want my code to be as simple as possible.

Any ideas?

Upvotes: 2

Views: 3077

Answers (2)

AlexCuse
AlexCuse

Reputation: 18296

I think if you want both on the same row you'll need a CASE statement. Something like this should work

select sum(case when voteValue = 1 then 1 else 0 end) as positiveVoteTotal,
           sum(case when voteValue = -1 then 1 else 0 end) as negativeVoteTotal
from leTable

I think you would be better off representing vote as a nullable bit/boolean type though (you could use same technique to query)

Upvotes: 2

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57650

SUM should not be used on ENUMs. Internally they are using integer values. But you should not use it for counting those numbers. You can group by the enum column and use count() on other columns to get how many votes per enum is casted.

See the following scenario.

CREATE TABLE `votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `vote` enum('PARTY_2','PARTY_1','PARTY_9') DEFAULT NULL,
  PRIMARY KEY (`user`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

insert into votes( user, vote) values( 1, 2), (2, 3), (4,3), (3, 1), (6,1), (5,2), (8, 2), (9,3);

SELECT vote `Party`, count(id) as `Votes`  FROM votes group by `vote`;

Output

+---------+-------+
| Party   | Votes |
+---------+-------+
| PARTY_2 |     2 |
| PARTY_1 |     3 |
| PARTY_9 |     3 |
+---------+-------+

Upvotes: 1

Related Questions