Overview

One of the critical considerations for software stability is the defect count by severity. Knowing the number of defects by the agreed upon severity rating will greatly help in reviewing deployment readiness and resource capacity planning during deployment and post-deployment. 

Software Stability SQL Query

Given below is the query.

select 
 o.detected_release_id, 
 sum(o.low) as low_defects, 
 sum(o.medium) as med_defects, 
 sum(o.high) as high_defects, 
 sum(o.critical) as crit_defects 
from 
(
  (select 
   R.DETECTED_RELEASE_ID, COUNT(R.INCIDENT_ID) as low, 0 as medium, 0 as high, 0 as Critical, 0 as total 
   from SpiraTestEntities.R_Incidents as R
   where R.PROJECT_ID = ${ProjectId} and R.DETECTED_RELEASE_ID is not null and  
   R.SEVERITY_ID = 1
   group by R.DETECTED_RELEASE_ID)
   union
 (select 
   R.DETECTED_RELEASE_ID, 0 as low,  COUNT(R.INCIDENT_ID) as medium, 0 as high, 0 as Critical, 0 as total 
   from SpiraTestEntities.R_Incidents as R
   where R.PROJECT_ID = ${ProjectId} and R.DETECTED_RELEASE_ID is not null and 
   R.SEVERITY_ID = 2
  group by R.DETECTED_RELEASE_ID)
  union
  (select 
   R.DETECTED_RELEASE_ID, 0 as low,  0 as medium, COUNT(R.INCIDENT_ID) as high, 0 as Critical, 0 as total 
   from SpiraTestEntities.R_Incidents as R
   where R.PROJECT_ID = ${ProjectId} and R.DETECTED_RELEASE_ID is not null and 
   R.SEVERITY_ID = 3
   group by R.DETECTED_RELEASE_ID)
  union
  (select 
   R.DETECTED_RELEASE_ID, 0 as low,  0 as medium, 0 as high, COUNT(R.INCIDENT_ID) as Critical, 0 as total 
   from SpiraTestEntities.R_Incidents as R
   where R.PROJECT_ID = ${ProjectId} and R.DETECTED_RELEASE_ID is not null and 
   R.SEVERITY_ID = 4
  group by R.DETECTED_RELEASE_ID)
  union
  (select 
   R.DETECTED_RELEASE_ID, 0 as low,  0 as medium, 0 as high, 0 as Critical,  COUNT(R.INCIDENT_ID) as total 
   from SpiraTestEntities.R_Incidents as R
   where R.PROJECT_ID = ${ProjectId} and R.DETECTED_RELEASE_ID is not null and R.SEVERITY_ID is not null
   group by R.DETECTED_RELEASE_ID)) as o
group by 
  o.detected_release_id

Query Explanation

  • This query contains an outer query that combines multiple subqueries using the union operation.
  • Each subquery counts the number of incidents in one of the four categories (low, medium, high, critical). 
  • According to the template, the severity id corresponds as follows: 1 - Low, 2 - High, 3 - High, 4 - Critical. If your template setting is different, you will have to modify and label the fields appropriately.
  • Since the incidents are counted, the release it is detected is required and hence DETECTED_RELEASE_ID is not null is used to eliminate where this information is unavailable.
  • In one of the subqueries for counting all the incidents in any of the four severity categories, the SEVERITY_ID is not null is used to eliminate incidents where the severity is unavailable. 
  • The outer query sums up all the low, medium, high, and critical defects by detected release. 
  • If specific types of incidents (like type = bug) is required depending upon incident types configured, then, that clause must be added to each subquery.

Output

Given below is the output.

Quality - Software Stability