Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PortletPaulCommented:
>>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
 
PortletPaulCommented:
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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