Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)
COURSE of the MONTH
14 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Getting Percentage Report From Table Data
Posted on 2013-06-06
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!
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!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
2
2
10 Comments
Active today
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
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
Active today
... 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
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
Active today
>>Is there a way to do that from a second query against the first query?
what first query?
what first query?
Active 6 days ago
Yes, I will award points when I've tried it and confirmed I have no further questions about it.
Active 6 days ago
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:
See my FROM line where I use a select clause.
How do I do that?
thanks!
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
See my FROM line where I use a select clause.
How do I do that?
thanks!
Active 6 days ago
oh shucks. nevermind. let me ask a new question about that after I figure out what I want to ask. thanks!
Featured Post
Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Long way back, we had to take help from third party tools in order to encrypt and decrypt data. Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash"
After Shake : http://www.videocopilot.net/presets/after_shake/
All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll
800 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.