SQL count column from another table

I have two tables in a database

The first being people which has the columns

id: int,
name: varchar(10)

and another being relationships which represents a one way following

me: int
following: int

Where me and following are foreign keys that match the id primary key on the person in table people.

I want to run a query that given a id of a person returns their name and the count of people that they are following and the number who are following them.

My current attempt is

SELECT *, COUNT(following.me), COUNT(following.following) FROM people
WHERE id = 3
JOIN following f1 on f1.me = id
JOIN following f2 on f2.following = id;

But it is throwing in error about the where syntax. I think I need to use group by somewhere but I am struggling as to how that works over multiple tables.

So say given id=2 it would return [{name: "sam", followers: 4, following: 3}]

Answer

This could be solved simply with inline correlated subqueries, like:

select 
    p.name,
    (select count(*) from relationships r where r.following = p.id) followers,
    (select count(*) from relationships r where r.me = p.id) following
from people p
where p.id = 3

This should be a quite efficient option.

Otherwise, starting from your existing query, you can also left join and aggregate:

select 
    p.name,
    count(distinct r.following) followers,
    count(distinct r.me) following
from people p
left join relationships r on p.id in (r.followers, r.me)
where p.id = 2
group by p.id, p.name