title: PSQL Group by multiple conditions on same row
body: <p>Let's say I have a table like this:</p>
  
  <pre><code>Letter  Color  
  A       Red    
  A       Blue    
  B       Red          
  C       Red
  C       Red
  </code></pre>
  
  <p>What I would like to achieve is the below output format:</p>
  
  <pre><code>Letter   Red   Blue
  A        1     1
  B        1     0
  C        2     0
  </code></pre>
  
  <p>Tried below:</p>
  
  <pre><code>SELECT letter, red, blue FROM (
  SELECT letter, count(*) AS red from letters where color = 'red'
  GROUP BY letter
  UNION
  SELECT letter, count(*) AS blue from letters where color = 'blue'
  GROUP BY letter
  ) GROUP BY letter
  </code></pre>
  
  <p>Can anyone help? Please note that I can't add any tablefunc or similar to DB.
  Thanks in advance</p>