title: SQL count column from another table
body: <p>I have two tables in a database</p>
  
  <p>The first being <code>people</code> which has the columns</p>
  
  <pre><code>id: int,
  name: varchar(10)
  </code></pre>
  
  <p>and another being <code>relationships</code> which represents a one way following</p>
  
  <pre><code>me: int
  following: int
  </code></pre>
  
  <p>Where <code>me</code> and <code>following</code> are foreign keys that match the <code>id</code> primary key on the person in table <code>people</code>.</p>
  
  <p>I want to run a query that given a <code>id</code> of a person returns their name and the count of people that they are following and the number who are following them.</p>
  
  <p>My current attempt is </p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>But it is throwing in error about the where syntax. I think I need to use <code>group by</code> somewhere but I am struggling as to how that works over multiple tables.</p>
  
  <p>So say given <code>id=2</code> it would return <code>[{name: "sam", followers: 4, following: 3}]</code></p>