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