Getting Percentage Report From Table Data

I have a table and it has information about training taken:
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

From that data, I want to create a table that just has percentages like:
Joe Blow Passed 66% Failed 33%
Jane Doe Passed 50% Failed 50%

Is there a way to do that from a second query against the first query?

I prefer a SQL query over a stored procedure if possible.

thanks!

Starr Duskk

Asked: 2013-06-06

Advertise Here

Microsoft SQL Server 2008

1 Solution

LVL 66

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented: 2013-06-06

Give this a whirl... (with test script)

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

LVL 66

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented: 2013-06-06

... cleaner ...

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(CAST(pass_count / CAST(total as decimal(3,0)) * 100 as decimal(5,2)) as varchar(10)) + '%' as passed_pct,
      'Failed ' + CAST(CAST((1-pass_count / CAST(total as decimal(3,0))) * 100 as decimal(5,2)) as varchar(10)) + '%' as failed_pct
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

LVL 2

Starr DuskkASP.NET VB.NET DeveloperAuthor Commented: 2013-06-06

great! that looks like the right direction. I'll let you know. thanks!

LVL 50

PortletPaulfreelancerCommented: 2013-06-06

>>Is there a way to do that from a second query against the first query?
what first query?

LVL 2

Starr DuskkASP.NET VB.NET DeveloperAuthor Commented: 2013-06-07

jimhorn figured out what I meant and answered.

LVL 50

PortletPaulfreelancerCommented: 2013-06-07

Excellent - he's a good guy: award the points and I'll leave you in peace :)

LVL 2

Starr DuskkASP.NET VB.NET DeveloperAuthor Commented: 2013-06-07

Yes, I will award points when I've tried it and confirmed I have no further questions about it.

LVL 2

Starr DuskkASP.NET VB.NET DeveloperAuthor Commented: 2013-06-07

Okay, jim, thanks. it's close. But I am not going to be inserting into a table. I will be creating a select query that creates the data that is in :
INSERT INTO #tmp (name, training, score)

So I need it to be something more like:

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

Open in new window

See my FROM line where I use a select clause.

How do I do that?

thanks!