How to separate boolean column values on mysql

Question

I have this table

create table votes(
    candidate varchar(20),
    voteup bool
)

and I want to display something like this

|candidate|vote ups(true)|vote downs(false)|vote points(true-false)
|hitler   |16            |104              |-88
|obama    |119           |1                |118

Sounds like you want a pivot: http://stackoverflow.com/questions/7674786/mysql-pivot-table. — siride, Feb 07 '15 at 05:53
BOOL and BOOLEAN in MySQL are synonyms for TINYINT(1) so you'd be dealing with a number — Mario, Feb 07 '15 at 06:10

score 3 · Accepted Answer · answered Feb 07 '15 at 06:16

3

You can "abuse" the fact that boolean values evaluate as 1 and 0 in integer operations:

select candidate,
    sum(voteup) as 'vote up',
    sum(voteup=0) as 'vote down',
    sum(if(voteup,1,-1)) as 'vote points'
from votes
group by candidate;

answered Feb 07 '15 at 06:16

Timothée Groleau

1,940
13
16

Mario · Answer 2 · 2015-02-07T06:28:20.950

0

SELECT candidate, 
       SUM(IF(voteup=0,0,1)) 'vote ups (true)',
       SUM(IF(voteup=0,1,0)) 'vote downs (false)',
       SUM(IF(voteup,1,-1)) 'vote points (true-false)'
FROM votes
GROUP BY candidate

I changed my original answer because BOOL / TINYINT(1) can contain any number between -128 and 127

edited Feb 07 '15 at 06:28

answered Feb 07 '15 at 06:07

Mario

196
1
8

How to separate boolean column values on mysql

2 Answers2