Count Of items, Count of Items Passed and percentage passed

[Jay Balapa]

Hello,

Underlying table has the following fields and values
Barcode Item Pass
1000 x YES
1001 x YES
1002 x NO
1003 x NO
1004 y YES
1005 y YES
1006 y NO


Iam trying to create a summary resultset which has the follwing-

Item, [Total # of items], [# of Items Passed], [Percentage Passed]
x 4 2 50%
y 3 2 66%



I know I have to group by item and use the count to get the total number of
items. But Iam baffled on how to get # items passed and Percentage.

Can this be accomplished in a single statement?

Any help will be greatly appreciated.

-Jay

 

[Anith Sen]

Lookup the PIVOT operator in SQL 2005.

For some solutions applicable in SQL 2000, refer to KBA (
support.microsoft.com ) : 175574.

 

[markc600]

select Item,
count(*) as [Total # of items],
sum(case when Pass='YES' then 1 else 0 end) as [# of Items
Passed],
cast(sum(case when Pass='YES' then 100 else 0 end)/count(*) as
varchar(10))+'%' as [Percentage Passed]
from mytable
group by Item

 

[Mike C#]

CREATE TABLE #items (Barcode VARCHAR(4),
Item VARCHAR(10),
Pass VARCHAR(3))

INSERT INTO #items (Barcode, Item, Pass)
SELECT '1000', 'x', 'YES'
UNION SELECT '1001', 'x', 'YES'
UNION SELECT '1002', 'x', 'NO'
UNION SELECT '1003', 'x', 'NO'
UNION SELECT '1004', 'y', 'YES'
UNION SELECT '1005', 'y', 'YES'
UNION SELECT '1006', 'y', 'NO'

SELECT i.Item, COUNT(i.Item) Total_Items,
(
SELECT COUNT(Barcode)
FROM #items
WHERE Pass = 'YES'
AND i.Item = Item
) Total_Passed,
((
SELECT COUNT(Barcode)
FROM #items
WHERE Pass = 'YES'
AND i.Item = Item
) * 100 / COUNT(i.Item)) Percentage_Passed
FROM #items i
GROUP BY i.Item

DROP TABLE #items

 

[Jay Balapa]

Thanks for the help. Works great.