April 11, 2012 at 11:54 am
Hi, thanks in advance.
I have a table that contains a column called Outcome. It can contain 4 possible values: Passed, Failed, NotExec, Blocked.
I need to total up all occurences of Passed, Failed, and Blocked and have it return a column called "TotalExecuted". Then I need to total up all occurences of all values and call that column "TotalPlanned"
I tried
SUM(count(select outcome from testsuite where outcome = passed), count(count(select outcome from testsuite where outcome = failed), count(select outcome from testsuite where outcome = blocked) as TotalExecuted...but this did not work.
I do not want to use a #temp table.
---DDL
create table TestSuite
(
Col1 int
, Test varchar(10)
, Outcome varchar (10)
)
insert into TestSuite
values
(1, 'test1', 'Passed'),
(2, 'test2', 'Failed'),
(3, 'test3', 'NotExec'),
(4, 'test4', 'Blocked'),
(5, 'test5', 'Passed'),
(6, 'test6', 'Passed')
-
April 11, 2012 at 12:01 pm
Use a CASE statement.
WITH TestSuite(Col1, Test, Outcome) AS (
SELECT 1, 'test1', 'Passed' UNION ALL
SELECT 2, 'test2', 'Failed' UNION ALL
SELECT 3, 'test3', 'NotExec' UNION ALL
SELECT 4, 'test4', 'Blocked' UNION ALL
SELECT 5, 'test5', 'Passed' UNION ALL
SELECT 6, 'test6', 'Passed'
)
SELECT
COUNT(CASE WHEN Outcome IN ('Passed', 'Failed', 'Blocked') THEN Col1 END) AS TotalExecuted
,COUNT(Col1) AS TotalPlanned
FROM TestSuite
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 11, 2012 at 12:04 pm
This is what I came up with.
create table dbo.TestSuite
(
Col1 int
, Test varchar(10)
, Outcome varchar (10)
)
insert into dbo.TestSuite
values
(1, 'test1', 'Passed');
insert into dbo.TestSuite
values
(2, 'test2', 'Failed');
insert into dbo.TestSuite
values
(3, 'test3', 'NotExec');
insert into dbo.TestSuite
values
(4, 'test4', 'Blocked');
insert into dbo.TestSuite
values
(5, 'test5', 'Passed');
insert into dbo.TestSuite
values
(6, 'test6', 'Passed');
SELECT * FROM dbo.TestSuite;
SELECT
SUM(CASE WHEN Outcome IN ('Passed','Failed','Blocked') THEN 1 ELSE 0 END) TotalExecuted,
SUM(CASE WHEN Outcome IN ('NotExec') THEN 1 ELSE 0 END) TotalPlanned
FROM
dbo.TestSuite;
DROP TABLE dbo.TestSuite;
April 11, 2012 at 12:25 pm
Thanks for both approaches. Went with drew.allen's because I think Lynn's doesn't get the sum total for all test cases ('Passed', 'Failed', 'Blocked', 'Not Executed') as TotalPlanned and alas I couldn't adjust it to get that.
Thanks to both of you!!!
Do I need to start another thread to ask the following?
I need to retrieve distinct outcomes so I have to start my select statement with Select distinct outcome, but I don't want the outcome column to be the first in the result set. However, I am hampered by the use of pivot which doesn't allow 'group by' clause.....
Anyway to get distinct outcome without it being first in column list?
New thread or will you answer here?
-
April 11, 2012 at 12:27 pm
xenophilia (4/11/2012)
Thanks for both approaches. Went with drew.allen's because I think Lynn's doesn't get the sum total for all test cases ('Passed', 'Failed', 'Blocked', 'Not Executed') as TotalPlanned and alas I couldn't adjust it to get that.Thanks to both of you!!!
Do I need to start another thread to ask the following?
I need my outcome column to retrieve with distinct statment, but I don't want the outcome column to be the first in the result set. However, I am hampered by the use of pivot which doesn't allow 'group by' clause.....
I need outcome values that are retrieved to be distinct.
New thread or will you answer here?
I have a better idea. You try writing the query first, then if you have problems start another thread and ask the question.
April 11, 2012 at 3:32 pm
OK back to the original DDL and general problem. That CASE statment works, however I don't have counts returning for two of those values. This would be fine except I want to import this query into Excel and need to have placeholders for those values that were not found. I would like 4 rows returned, with '0's and labels for respective values for those rows that have no counts.
-
April 11, 2012 at 3:53 pm
Sounds like you need a tally table[/url].
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 6:38 pm
xenophilia (4/11/2012)
OK back to the original DDL and general problem. That CASE statment works, however I don't have counts returning for two of those values. This would be fine except I want to import this query into Excel and need to have placeholders for those values that were not found. I would like 4 rows returned, with '0's and labels for respective values for those rows that have no counts.
Please explain in greater detail what is wrong.
April 11, 2012 at 8:22 pm
Being the contrarion that I am, I'll offer another solution.
DECLARE @t TABLE (id INT, test VARCHAR(10), status VARCHAR(10))
INSERT INTO @t (id, test, status)
SELECT 1, 'test1', 'Passed' UNION ALL
SELECT 2, 'test2', 'Failed' UNION ALL
SELECT 3, 'test3', 'NotExec' UNION ALL
SELECT 4, 'test4', 'Blocked' UNION ALL
SELECT 5, 'test5', 'Passed' UNION ALL
SELECT 6, 'test6', 'Passed'
;WITH PETests (PE, CountTests) AS (
SELECT s.PE, COUNT(test) As CountTests
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
,MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END) AS Planned
FROM PETests
Don't understand the bit about DISTINCT though. Could you elaborate?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 12, 2012 at 12:07 am
dwain.c (4/11/2012)
Being the contrarion that I am, I'll offer another solution.
DECLARE @t TABLE (id INT, test VARCHAR(10), status VARCHAR(10))INSERT INTO @t (id, test, status)
SELECT 1, 'test1', 'Passed' UNION ALL
SELECT 2, 'test2', 'Failed' UNION ALL
SELECT 3, 'test3', 'NotExec' UNION ALL
SELECT 4, 'test4', 'Blocked' UNION ALL
SELECT 5, 'test5', 'Passed' UNION ALL
SELECT 6, 'test6', 'Passed'
;WITH PETests (PE, CountTests) AS (
SELECT s.PE, COUNT(test) As CountTests
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
,MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END) AS Planned
FROM PETests
Don't understand the bit about DISTINCT though. Could you elaborate?
With all due respect to your solution Dwayne, I think planned would be the total of all pf the exams ie: passed+failed+blocked+ Not Exec.
So, I tweaked your solution and got this:
DECLARE @t TABLE (id INT, test VARCHAR(10), status VARCHAR(10))
INSERT INTO @t (id, test, status)
SELECT 1, 'test1', 'Passed' UNION ALL
SELECT 2, 'test2', 'Failed' UNION ALL
SELECT 3, 'test3', 'NotExec' UNION ALL
SELECT 4, 'test4', 'Blocked' UNION ALL
SELECT 5, 'test5', 'Passed' UNION ALL
SELECT 6, 'test6', 'Passed'
;WITH PETests (PE, CountTests, TotalCount) AS (
SELECT s.PE, COUNT(t.test) As CountTests, Count(t.status) As TotalCount
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
--,MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END) AS Planned,
,(MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) + MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END)) As TotalPlanned
FROM PETests
Hope This is what was required.
Vinu Vijayan
April 12, 2012 at 12:41 am
Vinu,
You may be right. The OP didn't provide a good visual of expected results.
When I think about transforming data from A --> B (regardless of how many steps there are in between), I work much better seeing actual data and not trying to interpret a description. Especially when I often find myself thinking in different terms for the transformations required (than the OP).
I call that step visualizing the transformations.
Much easier than translating English to an algorithm. 🙂
Whatever works for the OP is fine by me.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 12, 2012 at 7:46 am
vinu512 (4/12/2012)
dwain.c (4/11/2012)
Being the contrarion that I am, I'll offer another solution.
DECLARE @t TABLE (id INT, test VARCHAR(10), status VARCHAR(10))INSERT INTO @t (id, test, status)
SELECT 1, 'test1', 'Passed' UNION ALL
SELECT 2, 'test2', 'Failed' UNION ALL
SELECT 3, 'test3', 'NotExec' UNION ALL
SELECT 4, 'test4', 'Blocked' UNION ALL
SELECT 5, 'test5', 'Passed' UNION ALL
SELECT 6, 'test6', 'Passed'
;WITH PETests (PE, CountTests) AS (
SELECT s.PE, COUNT(test) As CountTests
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
,MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END) AS Planned
FROM PETests
Don't understand the bit about DISTINCT though. Could you elaborate?
With all due respect to your solution Dwayne, I think planned would be the total of all pf the exams ie: passed+failed+blocked+ Not Exec.
So, I tweaked your solution and got this:
DECLARE @t TABLE (id INT, test VARCHAR(10), status VARCHAR(10))
INSERT INTO @t (id, test, status)
SELECT 1, 'test1', 'Passed' UNION ALL
SELECT 2, 'test2', 'Failed' UNION ALL
SELECT 3, 'test3', 'NotExec' UNION ALL
SELECT 4, 'test4', 'Blocked' UNION ALL
SELECT 5, 'test5', 'Passed' UNION ALL
SELECT 6, 'test6', 'Passed'
;WITH PETests (PE, CountTests, TotalCount) AS (
SELECT s.PE, COUNT(t.test) As CountTests, Count(t.status) As TotalCount
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
--,MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END) AS Planned,
,(MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) + MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END)) As TotalPlanned
FROM PETests
Hope This is what was required.
Vinu Vijayan
you can actually simplify that a little more (just the cte and query)
;WITH PETests (PE, CountTests, TotalCount) AS (
SELECT s.PE, COUNT(t.test) As CountTests, Count(t.status) As TotalCount
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
, SUM(TotalCount) As TotalPlanned
FROM PETests
why use case statements and add them when we have 2 rows in our cte and can just sum them
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 12, 2012 at 10:18 pm
capn.hector (4/12/2012)
vinu512 (4/12/2012)
dwain.c (4/11/2012)
Being the contrarion that I am, I'll offer another solution.
DECLARE @t TABLE (id INT, test VARCHAR(10), status VARCHAR(10))INSERT INTO @t (id, test, status)
SELECT 1, 'test1', 'Passed' UNION ALL
SELECT 2, 'test2', 'Failed' UNION ALL
SELECT 3, 'test3', 'NotExec' UNION ALL
SELECT 4, 'test4', 'Blocked' UNION ALL
SELECT 5, 'test5', 'Passed' UNION ALL
SELECT 6, 'test6', 'Passed'
;WITH PETests (PE, CountTests) AS (
SELECT s.PE, COUNT(test) As CountTests
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
,MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END) AS Planned
FROM PETests
Don't understand the bit about DISTINCT though. Could you elaborate?
With all due respect to your solution Dwayne, I think planned would be the total of all pf the exams ie: passed+failed+blocked+ Not Exec.
So, I tweaked your solution and got this:
DECLARE @t TABLE (id INT, test VARCHAR(10), status VARCHAR(10))
INSERT INTO @t (id, test, status)
SELECT 1, 'test1', 'Passed' UNION ALL
SELECT 2, 'test2', 'Failed' UNION ALL
SELECT 3, 'test3', 'NotExec' UNION ALL
SELECT 4, 'test4', 'Blocked' UNION ALL
SELECT 5, 'test5', 'Passed' UNION ALL
SELECT 6, 'test6', 'Passed'
;WITH PETests (PE, CountTests, TotalCount) AS (
SELECT s.PE, COUNT(t.test) As CountTests, Count(t.status) As TotalCount
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
--,MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END) AS Planned,
,(MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) + MAX(CASE PE WHEN 'P' THEN CountTests ELSE 0 END)) As TotalPlanned
FROM PETests
Hope This is what was required.
Vinu Vijayan
you can actually simplify that a little more (just the cte and query)
;WITH PETests (PE, CountTests, TotalCount) AS (SELECT s.PE, COUNT(t.test) As CountTests, Count(t.status) As TotalCount
FROM (
SELECT 'Passed', 'E' UNION ALL
SELECT 'Failed', 'E' UNION ALL
SELECT 'NotExec', 'P' UNION ALL
SELECT 'Blocked', 'E') s(status, PE)
LEFT JOIN @t t ON s.status = t.status
GROUP BY s.PE)
SELECT MAX(CASE PE WHEN 'E' THEN CountTests ELSE 0 END) AS Executed
, SUM(TotalCount) As TotalPlanned
FROM PETests
why use case statements and add them when we have 2 rows in our cte and can just sum them
You are right capn.
I tried the Sum(TotalCount), but somehow it was not returning what I wanted. Maybe something wrong I had written in the code. That's when I added the results of the Max functions to get it to work.
But now that I tried it again, Sum(TotalCount) works perfectly.
Thanx for the poke Capn.
April 13, 2012 at 9:21 am
vinu512 (4/12/2012)
You are right capn.
I tried the Sum(TotalCount), but somehow it was not returning what I wanted. Maybe something wrong I had written in the code. That's when I added the results of the Max functions to get it to work.
But now that I tried it again, Sum(TotalCount) works perfectly.
Thanx for the poke Capn.
no problem. the reason for the max is to choose only the executed tests since you are grouping by the 'E' or 'P' on the initial query. so summing the 2 rows get you the total.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 13, 2012 at 11:59 pm
Wow. Looks like while I was battling other fires you guys did some of my work for me. I will now look this over and see and get back to you. Thanks, because that means i may actually get some sleep.
The OP
-
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply