CODEWITHSUNDEEP
mysqlsumcalculated-field
directory
directory

Reputation: 3167

mysql resturn value as min or plus from sum

For my vote system I have two possible values to vote for. 1 or -1, this exactly value is saved in the database.

Right now I select/count the values separated like field_up and field_down. I would like to change my query to output just one value like:

if we have '+6' and -5 it should return +1 (with +) if we have -3 and +2 I it should return -1 (with -)

for now I have the follow query

SELECT 
SUM(CASE thumb when 1 then 1 else 0 end) AS "thumbs_up", 
SUM(CASE thumb when -1 then 1 else 0 end) AS "thumbs_down" 
FROM ..etc

Thanks in advance!

Thea.

Upvotes: 1

Views: 169

Answers (3)

Bohemian
Bohemian

Reputation: 425033

Couple of things. You can simplify your query considerably to this:

SELECT 
SUM(thumb = 1) AS thumbs_up, 
SUM(thumb = -1) AS thumbs_down 
FROM ..etc

because in mysql (only) true is 1 and false is 0.

Secondly, you need to query over that query to tweak the output:

SELECT concat(if(thumbs_up > thumbs_down, '+', ''), thumbs_up - thumbs_down)
FROM (
SELECT 
    SUM(thumb = 1) AS thumbs_up, 
    SUM(thumb = -1) AS thumbs_down
    FROM ..etc
) x

Upvotes: 0

Jocelyn
Jocelyn

Reputation: 11393

SELECT SUM(thumb) AS votes FROM your_table

Upvotes: 1

PSR
PSR

Reputation: 40318

try this

SELECT 
SUM( CASE WHEN thumb =1 THEN 1 ELSE 0 END) as "thumbs_up", 
SUM( CASE WHEN thumb =-1 THEN 1 ELSE 0 END) as "thumbs_down",

FROM ..etc

Upvotes: 0

Related Questions