Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
SELECT name, 'Passed ' + CAST(pass_count / CAST(total as decimal(5,2)) * 100 as varchar(10)) + '%'
FROM (
SELECT name, SUM(CASE score WHEN 'Pass' THEN 1 WHEN 'Fail' THEN 0 END) as pass_count, COUNT(score) as total
FROM (Select name, training, score from trainingtable)
GROUP BY name) a
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
DROP TABLE #tmp
GO
CREATE TABLE #tmp (name varchar(10), training varchar(10), score varchar(10))
INSERT INTO #tmp (name, training, score)
VALUES
('Joe Blow', 'Training 1', 'Pass'),
('Joe Blow', 'Training 2', 'Fail'),
('Joe Blow', 'Training 3', 'Pass'),
('Jane Doe', 'Training 2', 'Pass'),
('Jane Doe', 'Training 3', 'Fail')
SELECT name, 'Passed ' + CAST(pass_count / CAST(total as decimal(5,2)) * 100 as varchar(10)) + '%'
FROM (
SELECT name, SUM(CASE score WHEN 'Pass' THEN 1 WHEN 'Fail' THEN 0 END) as pass_count, COUNT(score) as total
FROM #tmp
GROUP BY name) a