Sequelize Subtraction of counts

Question

I am trying to get the count by subtracting all records that have false from all the records that have true. I am unable to replicate such a thing in Sequelize but I was able to create it in a raw query:

SELECT (SELECT Count(id) 
    FROM   votes 
    WHERE  up = true 
           AND id = 1) - (SELECT Count(id) 
                                   FROM   votes 
                                   WHERE  up = false 
                                          AND id = 1) AS votes

Based off the documentation, I do not see a way to do such a thing. Is there anything that I am missing?

a `case expression` would make this simpler, and I imagine therefore easier in your js library — Paul Maxwell, Sep 19 '18 at 02:45

score 1 · Accepted Answer · answered Sep 19 '18 at 02:48

This may be simpler for your js lib to handle

select count(case when up = true then 1 end) - count(case when up = false then 1 end)
from votes
where id = 1

then this previous answer should help I think: https://stackoverflow.com/a/47397320/2067753

Note that the count() function ignores nulls, but using sum() instead of count can give the same end result:

select sum(case when up = true then 1 else 0 end) - sum(case when up = false then 1 else 0 end)
from votes
where id = 1

using group by:

select id, count(case when up = true then 1 end) - count(case when up = false then 1 end)
from votes
where id = 1
group by id

Sequelize Subtraction of counts

1 Answers1