Q:

Can you use EXISTS after a query has run?

Imagine I have a table like this:
followed | follower
-------------------
   2     |    1
   1     |    2
   4     |    2

I am trying to get the:

follower count (how many people follow user 2) (in this case, 1)
follow count (how many people user 2 follows) (in this case, 2)
whether or not user 1 follows user 2 (here, true)

Intuitively, this is very simple. However, I cannot seem to get the SQL query to work as expected. This is the query I am using so far:
SELECT
  COUNT(NULLIF(f.followed, 2)) AS follows,
  COUNT(NULLIF(f.follower, 2)) AS followers,
  COUNT(NULLIF(f.follower, 1)) = 1 AS user_follows
FROM follows f
WHERE 2 IN (f.followed, f.follower)

However, this both returns false and is not even accurate as when I change it to COUNT(NULLIF(f.follower, 1)) it sets user_follows to 2 because user 1 follows 2 people. I want to only filter based on the table returned from the FROM...WHERE clause and not hit the database any more, as I already have all the necessary data! I'm new to SQL so sorry this is a basic question.

A:

In Postgres, you can use the filter clause:
SELECT COUNT(*) FILTER (WHERE f.followed = 2) as follows,
       COUNT(*) FILTER (WHERE f.follower = 2) as follers,
       COUNT(*) FILTER (WHERE f.follower = 1 AND f.followed = 2) as user_1_follows_2
FROM follows f
WHERE 2 IN (f.followed, f.follower);

