title: How to count multiple things from a big JOIN of tables
body: <p>I've always seen COUNT() used with * as argument and the GROUP BY determining what was meant to be counted, but i'm convinced you can count different things without having to do sub-queries, group by, then join (which is what I'm doing right now).</p>
  
  <p>Let's build an example out of a simplified version of SO:</p>
  
  <pre><code>Table Question
  idQuestion | title | idUser
  
  Table Answer
  idAnswer | idQUestion | text | idUser
  
  Table Vote
  idVote | idAnswer | type | idUser
  
  Table Comment
  idComment | idAnswer | text | idUser 
  </code></pre>
  
  <p>To know how many comments and votes all answers have I'd do:</p>
  
  <pre><code>SELECT idAnswer, votes, comments FROM
     (SELECT idAnswer, count(*) AS votes FROM Answer A
         JOIN Vote B ON A.idAnswer=B.idAnswer
      GROUP BY idAnswer) AS X
  JOIN
     (SELECT idAnswer, count(*) AS comments FROM Answer A
         JOIN Comment B ON A.idAnswer=B.idAnswer
      GROUP BY idAnswer) AS Y
  ON X.idAnswer=Y.idAnswer
  </code></pre>
  
  <p>Can this be done without sub-selects? is anything achieved by avoiding them?</p>
  
  <p><strong>how can you also count negative votes and positive votes on the same query?</strong></p>