SQL: PIVOTting Count & Percentage against a column

aggregate, pivot, sql-server-2008-r2, t-sql

Solution

It sounds like you just need to add a column for Percent Passed and Percent Failed. You can calculate those columns on your `PIVOT`.

SELECT r2.PartNo
    , [Pass] AS Passed
    , [Fail] as Failed
    , ([Pass] / Cast(([Pass] + [Fail]) as decimal(5, 2))) * 100 as PercentPassed
    , ([Fail] / Cast(([Pass] + [Fail]) as decimal(5, 2))) * 100 as PercentFailed
FROM
(
    SELECT ResultID, PartNo, Result 
    FROM Results
) r1
PIVOT 
(
    Count(ResultID) 
    FOR Result IN ([Pass], [Fail])
) AS r2
ORDER By r2.PartNo

Problem

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. So far, I have the following: ``` 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 ``` This is half of the solution (the totals for passes and fails); the question is, how do I push on and include percentages? 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. Can I extend this PIVOT query, or should I take a different approach? DDL: ``` CREATE TABLE RESULTS ( [ResultID] [int] NOT NULL, [SerialNo] [int] NOT NULL, [PartNo] [varchar](10) NOT NULL, [Result] [varchar](10) NOT NULL); ``` DML: ``` 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') ``` UPDATE: My solution, based on bluefeet's answer is: ``` 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 ``` 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.

Original source