Overview
One of the critical considerations for quality is the efficiency of test efficiency. With every release, the number of identified test cases that have passed should be more than the test cases that are in not applicable, not run, or failed statuses. While these statues other than passed is not always an indication that the release is not ready for deployment, careful consideration must be evaluated to see why these test cases have not passed and what process improvements must be in place.
Regression Test Efficiency SQL Query
Given below is the query.
select o.release_id, sum(o.Not_Applicable) as NA, sum(o.Not_Run) as NotRun, sum(o.Passed) as Passed, sum(o.Failed) as Failed from
(
(select R.RELEASE_ID, COUNT(R.TEST_CASE_ID) as Failed, 0 as Passed, 0 as Not_Applicable, 0 as Not_Run, 0 as Total
from SpiraTestEntities.R_ReleaseTestCases as R
join SpiraTestEntities.R_TestCases as TC on R.TEST_CASE_ID = TC.TEST_CASE_ID
where R.PROJECT_ID = ${ProjectId} and TC.EXECUTION_STATUS_NAME = "Failed"
group by R.RELEASE_ID)
union
(select R.RELEASE_ID, 0 as failed, COUNT(R.TEST_CASE_ID) as Passed, 0 as Not_Applicable, 0 as Not_Run, 0 as Total
from SpiraTestEntities.R_ReleaseTestCases as R
join SpiraTestEntities.R_TestCases as TC on R.TEST_CASE_ID = TC.TEST_CASE_ID
where R.PROJECT_ID = ${ProjectId} and TC.EXECUTION_STATUS_NAME = "Passed"
group by R.RELEASE_ID)
union
(select R.RELEASE_ID, 0 as failed, 0 as Passed, COUNT(R.TEST_CASE_ID) as Not_Applicable, 0 as Not_Run, 0 as Total
from SpiraTestEntities.R_ReleaseTestCases as R
join SpiraTestEntities.R_TestCases as TC on R.TEST_CASE_ID = TC.TEST_CASE_ID
where R.PROJECT_ID = ${ProjectId} and TC.EXECUTION_STATUS_NAME = "N/A"
group by R.RELEASE_ID)
union
(select R.RELEASE_ID, 0 as failed, 0 as Passed, 0 as Not_Applicable, COUNT(R.TEST_CASE_ID) as Not_Run, 0 as Total
from SpiraTestEntities.R_ReleaseTestCases as R
join SpiraTestEntities.R_TestCases as TC on R.TEST_CASE_ID = TC.TEST_CASE_ID
where R.PROJECT_ID = ${ProjectId} and TC.EXECUTION_STATUS_NAME = "Not Run"
group by R.RELEASE_ID)
union
(select R.RELEASE_ID, 0 as failed, 0 as Passed, 0 as Not_Applicable, 0 as Not_Run, COUNT(R.TEST_CASE_ID) as Total
from SpiraTestEntities.R_ReleaseTestCases as R
join SpiraTestEntities.R_TestCases as TC on R.TEST_CASE_ID = TC.TEST_CASE_ID
where R.PROJECT_ID = ${ProjectId}
group by R.RELEASE_ID)
) as o
group by
o.release_id
Query Explanation
- This query uses an outer query to aggregate results from a collection of inner subqueries combined by the union operation.
- Each inner subquery counts the number of test cases at the release level joined with the test case table for that project but for a specific test case status (passed, failed, not run, not applicable) as determined by the template.
Output
Given below is the output.
![Quality - Regression Test Efficiency](/Support/Attachment/151376.aspx)