CODEWITHSUNDEEP
sqldatabasesqlite
David Nelband
David Nelband

Reputation: 427

SQLite - select from table a based on result of calculation of relevant rows in table b

i have two tables, "topics" (table a) and "topic_votes" (table b). each row in table b has an attribute, "vote" (boolean).

i need to select rows from topics and order them by the sum total of all relevant rows in topic_votes with vote=true subtracted by all relevant rows in topic_votes with vote=false.

for example, for one row in "topics" there are four total rows in "topic_vote" , 3 with vote=true and 1 with vote=false, in this case the "sum_total" will be 2 (3 - 1).

how can i implement this in sqlite?

Upvotes: 1

Views: 240

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can find the difference in vote count for each topic in subquery and then join it with topics table like this:

select t.*
from topics t
join (
    select topic_id, 
        sum(case when topic_vote = 'true' then 1 else 0 end) -
        sum(case when topic_vote = 'false' then 1 else 0 end) diff
    from topic_votes
    group by topic_id
) v on t.topic_id = v.topic_id
order by v.diff desc, t.topic_id;

I assumed the relation column is topic_id. Feel free to set it to whatever column name you have.

Upvotes: 1

Related Questions