Can I reduce this query?

Question

I have a table name likes which stores details about likes and dislikes for a post. To show the total number of likes for a post I need to take the diff between likes and dislikes. Now I am storing likes inside a column called liked which is of type bool. The idea is if liked then set it to true and disliked to false. My table looks like this,

| id | user_id | post_id | liked | 
| -- | ------- | ------- | ----- |
| 1  | 1       | 1       | true  |
| 2  | 7       | 1       | false |
| 3  | 12      | 1       | true  |

I used SELECT liked, COUNT(*) FROM likes WHERE post_id=1 GROUP BY liked;, which returned

| liked | count |
| ----- | ----- |
| TRUE  | 2     |
| FALSE | 1     |

I want to take the difference between these two. Couldn't seem to find anything so I tried
SELECT SUM((SELECT COUNT(*) FROM likes WHERE post_id=1 AND liked = true) - (SELECT COUNT(*) FROM likes WHERE post_id=1 AND liked = false));

which returns what I want

| SUM |
| --- |
| 1   |

But still I feel like this query is not right. Is there a better way to write this query?

Answer 1

A general solution is to do the aggregation in two steps:

select max(cnt) - min(cnt)
from
(
  select count(*) cnt FROM likes WHERE post_id=1 GROUP BY liked
) dt

In your case, with only two different values you can use conditional aggregation:

select abs(count(*) filter (where c1 = true) - count(*) filter (where c1 = false))
from b
WHERE post_id=1