Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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!
0
Starr Duskk
Asked:
Starr Duskk
  • 6
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
... 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
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
great! that looks like the right direction. I'll let you know. thanks!
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
PortletPaulfreelancerCommented:
>>Is there a way to do that from a second query against the first query?
what first query?
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
jimhorn figured out what I meant and answered.
0
 
PortletPaulfreelancerCommented:
Excellent - he's a good guy: award the points and I'll leave you in peace :)
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Yes, I will award points when I've tried it and confirmed I have no further questions about it.
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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!
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
oh shucks. nevermind. let me ask a new question about that after I figure out what I want to ask. thanks!
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Thanks for your help!
0
Question has a verified solution.

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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now