title: how do I aggregate one column for 2 tests into two columns on one row
body: <p>I have a table Called "PassFailMaster" with a PassFail field that is 1 for Pass; 0 for fail. <BR>
  I need to count the number of passes and fails and reveal them on the same row. I tried 2 aggregates and a union but I end up with 2 rows for each test type.<BR>
  Here's a sample of data:</p>
  
  <pre><code>CodeName    CodeDesc    TestNumber  PassFail
  v1      Vibration   1           0
  V1      Vibration   2           0
  v1      Vibration   3           1
  s1      Shock       4           1
  s1      Shock       5           1
  s1      Shock       6           0
  </code></pre>
  
  <p>Here's my query</p>
  
  <pre><code>SELECT        CodeName, CodeDesc, COUNT(CodeName) AS PassValues, 0 as FailValues
  FROM            PassFailMaster
  WHERE        (PassFail = 1)
  GROUP BY CodeName,  CodeDesc
  
  UNION
  
  SELECT        CodeName, CodeDesc,  0 AS PassValues, COUNT(CodeName) AS FailValues
  FROM            PassFailMaster
  WHERE        (PassFail = 0)
  GROUP BY CodeName,  CodeDesc
  ORDER BY CodeName
  </code></pre>
  
  <p>Here's what the data ends up looking like:</p>
  
  <pre><code>CodeName CodeDesc   PassValues FailValues
  s1      Shock       0           1
  s1      Shock       2           0
  v1      Vibration   0           2
  v1      Vibration   1           0
  </code></pre>
  
  <p>I need the result for passes &amp; fails to end up on one row like this.</p>
  
  <pre><code>CodeName CodeDesc PassValues FailValues
  s1      Shock       2           1
  v1      Vibration   1           2
  </code></pre>
  
  <p>Any ideas?</p>
  
  <p>Thanks - Mike</p>