title: SQL: PIVOTting Count & Percentage against a column
body: <p>I'm trying to produce a report that shows, for each Part No, the results of tests on those parts in terms of the numbers passed and failed, and the percentages passed and failed. </p>
  
  <p>So far, I have the following:</p>
  
  <pre><code>SELECT r2.PartNo, [Pass] AS Passed, [Fail] as Failed
      FROM
      (SELECT ResultID, PartNo, Result FROM Results) r1
  PIVOT (Count(ResultID) FOR Result IN ([Pass], [Fail])) AS r2
  ORDER By r2.PartNo
  </code></pre>
  
  <p>This is half of the solution (the totals for passes and fails); the question is, how do I push on and include percentages?</p>
  
  <p>I haven't tried yet, but I imagine that I can start again from scratch, and build up a series of subqueries, but this is more a learning exercise - I want to know the 'best' (most elegant or most efficient) solution, so I thought I'd seek advice.</p>
  
  <p><em>Can I extend this PIVOT query, or should I take a different approach?</em></p>
  
  <p>DDL:</p>
  
  <pre><code>CREATE TABLE RESULTS (
      [ResultID] [int] NOT NULL,
      [SerialNo] [int] NOT NULL,
      [PartNo] [varchar](10) NOT NULL,
      [Result] [varchar](10) NOT NULL);
  </code></pre>
  
  <p>DML:</p>
  
  <pre><code>INSERT INTO Results VALUES (1, '100', 'ABC', 'Pass')
  INSERT INTO Results VALUES (2, '101', 'DEF', 'Pass')
  INSERT INTO Results VALUES (3, '100', 'ABC', 'Fail')
  INSERT INTO Results VALUES (4, '102', 'DEF', 'Pass')
  INSERT INTO Results VALUES (5, '102', 'DEF', 'Pass')
  INSERT INTO Results VALUES (6, '102', 'DEF', 'Fail')
  INSERT INTO Results VALUES (7, '101', 'DEF', 'Fail')
  </code></pre>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>My solution, based on bluefeet's answer is:</p>
  
  <pre><code>SELECT r2.PartNo, 
      [Pass] AS Passed, 
      [Fail] as Failed,
      ROUND(([Fail] / CAST(([Pass] + [Fail]) AS REAL)) * 100, 2) AS PercentFailed
      FROM
      (SELECT ResultID, PartNo, Result FROM Results) r1
  PIVOT (Count(ResultID) FOR Result IN ([Pass], [Fail])) AS r2
  ORDER By r2.PartNo
  </code></pre>
  
  <p>I've ROUNDed a FLOAT(rather than CAST to DECIMAL twice) because its a tiny bit more efficient, and I've also decided that we only real need the failure %age.</p>