This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.
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 1 day ago
>>Is there a way to do that from a second query against the first query?
what first query?
what first query?
Active 2 days ago
Yes, I will award points when I've tried it and confirmed I have no further questions about it.
Active 2 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 2 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
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article.
SARGable is an adjective in SQL that means that an item can be fou…
Hi all,
It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month8 days, 21 hours left to enroll
604 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.