August 29, 2003 at 8:05 am
Let's say you've got a database that tracks tests. Simplified dramatically, there's a table that holds the test script itself, and another that holds instances of that test being executed. For each instance record, the CaseID is included, as well as a single field with a number indicating wheter it passed, failed, or was deferred.
I want a single output using a SQL script that will display each test case ID followed by how many passes, failures, and deferrals it has had, all on a single line in the resulting grid.
At first I was thinking to use a join, since I really am joining a table on itself a couple of times to stack the columns together, but I just couldn't get the syntax right - it keeps apreading the data across all three fields. Here's what I have so far, but I know I'm way off:
SELECT a1.caseID, count(a1.CaseID) as Passed, count(a2.CaseID) as Failed, count(a3.CaseID) as Deferred
FROM assignment a1 JOIN assignment a2 on a1.CaseID = a2.CaseID
JOIN assignment a3 ON a2.caseID = a3.CaseID
WHERE a1.StatusID = 4 OR a2.StatusID = 3 OR a3.StatusID = 2
GROUP BY a1.CaseID
ORDER BY a1.CaseID
I ended up using a union just to get some data on paper, but it stacks the 3 flavors of result into 3 separate lines rather than in one, and looks lousy. Any thoughts?
Edited by - buktseat on 08/29/2003 08:06:13 AM
August 29, 2003 at 8:29 am
buktseat, Interesting request to have all the output in a single field. I assume this is for display.
Ordinarily, I would have done
SELECT CaseID,
SUM( CASE StatisID WHEN 4 THEN 1 ELSE 0 END ) 'Passed',
SUM( CASE StatisID WHEN 3 THEN 1 ELSE 0 END ) 'Failed',
SUM( CASE StatisID WHEN 2 THEN 1 ELSE 0 END ) 'Deferred'
FROM assignment
Group by CaseID
Seems like, if you want all this on one line, you need to just concatenate the columns (probably convert them into text).
Guarddata-
August 29, 2003 at 8:48 am
I'm a little sketchy on the layout of your tables and data, but here's a couple things to try:
a subselect should work:
select TestCase.caseID, (select count(*) from assignment a1 where a1.caseID = TestCase.caseID and a1.statusID = 4) as Passed, (select count(*) from assignment a2 where a2.caseID = TestCase.caseID and a2.statusID = 3) as Failed, (select count(*) from assignment a3 where a3.caseID = TestCase.caseID and a3.statusID = 2) as Deferred
from TestCase
or, for more fun:
select caseID, sum(case when statusID = 4 then 1 else 0 end) as Passed, sum(case when statusID = 3 then 1 else 0 end) as Failed, sum(case when statusID = 2 then 1 else 0 end) as Deferred, count(*) as Total
from assignment
group by caseID
August 29, 2003 at 10:52 am
That last one did it. Thank's a lot guys!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply