title: Double counts when left joining in PostgreSQL
body: <p>I have the following tables:</p>
  
  <p><strong>users</strong> which have the following columns:</p>
  
  <pre><code>id: INT
  name: VARCHAR
  boss_id: INT
  </code></pre>
  
  <p><strong>bosses</strong> which have the following columns:</p>
  
  <pre><code>id: INT
  name: VARCHAR
  </code></pre>
  
  <p><strong>messages</strong> which have the following columns:</p>
  
  <pre><code>author_id: INT (reference to users)
  body: VARCHAR
  type: VARCHAR
  </code></pre>
  
  <p><strong>messages_targets</strong> which have the following columns:</p>
  
  <pre><code>user_id: INT (reference to users)
  message_id: INT (reference to messages)
  </code></pre>
  
  <p>Now, I have the following query that is properly returning me <strong>for each of the bosses, the percentage of users who have received at least one message of type 'urgent'</strong> This is how I have done the query:</p>
  
  <pre><code>SELECT (COUNT(DISTINCT CASE WHEN messages.type = 'urgent' THEN users.id END)::float / NULLIF(COUNT(DISTINCT users.id)::float, 0)) * 100,
  bosses.id
  FROM bosses
  LEFT JOIN users ON users.boss_id = bosses.id
  LEFT JOIN messages_targets ON messages_targets.user_id = users.id
  LEFT JOIN messages ON messages.id = messages_targets.message_id
  GROUP BY bosses.id
  </code></pre>
  
  <p>Now, I want to modify that query so that it also returns me, the count of urgent messages that the users have authored grouped by their boss. So, I have tried this:</p>
  
  <pre><code>SELECT (COUNT(DISTINCT CASE WHEN messages.type = 'urgent' THEN users.id END)::float / NULLIF(COUNT(DISTINCT users.id)::float, 0)) * 100 as percentage_received,
  COUNT(CASE WHEN authored_messages.type = 'urgent' THEN 1 END) authored_messages_count
  bosses.id
  FROM bosses
  LEFT JOIN users ON users.boss_id = bosses.id
  LEFT JOIN messages_targets ON messages_targets.user_id = users.id
  LEFT JOIN messages ON messages.id = messages_targets.message_id
  LEFT JOIN messages authored_messages ON messages.author_id = users.id
  GROUP BY bosses.id
  </code></pre>
  
  <p>But this is not working. It seems is double counting some data.</p>
  
  <p>Here is some sample data, and following on what I would expect:</p>
  
  <pre><code>bosses (id, name)
  1, John
  2, Charles
  
  users (id, name, boss_id)
  1, Mai, 1
  2, Donald, 1
  3, Denver, 2
  
  messages (author_id, body, type)
  1, 'message from Mai to Donald', 'urgent'
  2, 'message from Donald to Denver', 'normal'
  3, 'message from Denver to Mai', 'urgent'
  4, 'message from Mai to Donald', 'urgent'
  
  messages_targets (user_id, message_id)
  2, 1
  3, 2
  1, 3 
  2, 4
  </code></pre>
  
  <p>I would expect to get the following: </p>
  
  <pre><code>boss_id, percentage_received, authored_messages
  
  1, 100, 2 # (Both Mai and Donald received urgent messages, and in total there were 2 urgent messages sent)
  2, 0, 1 # (Denver did not receive any urgent messages, but he sent one message)
  </code></pre>