SELECT  Y.TotalTestCases, Y.PassedCases, Y.FailedCases, Z.TotalRequirements, Z.Requirementswithcoverage, Z.Requirementswithnocoverage
FROM (
Select F.Requirements, Count(F.TestName)AS 'TotalTestCases', COUNT(CASE WHEN F.STATUS  = 'Passed' Then F.STATUS END) AS "PassedCases", COUNT(CASE WHEN F.STATUS  = 'Failed' Then F.STATUS END) AS "FailedCases", COUNT(CASE WHEN F.STATUS  = 'NoRun' Then F.STATUS END) AS "NoRunCases", COUNT(CASE WHEN F.STATUS  = 'NotCompleted' Then F.STATUS END) AS "NotCompletedCases", COUNT(CASE WHEN F.STATUS  = 'N/A' Then F.STATUS END) AS "NACases",
Round ( Count(Case when F.STATUS  = 'Passed' Then F.STATUS End) * 100/
       CAST ( Count(F.TestName) AS float(2)) , 1) AS  '%Passed',
Round ( Count(Case when F.STATUS  = 'Failed' Then F.STATUS End) * 100/
       CAST ( Count(F.TestName) AS float(2)) , 1) AS  '%Failed'
From ( Select  Distinct T.TS_NAME AS "TestName", T.STATUS, T.Requirements
From ( SELECT TEST.TS_NAME, REQ.RQ_REQ_PATH, TEST.TS_EXEC_STATUS AS STATUS , Requirements = Case
   WHEN RQ_REQ_PATH Like 'AAAAADAARAADAAC%' Then 'Medicare'
   WHEN RQ_REQ_PATH Like 'AAAAADAALAAN%' THEN 'MHSA'
   Else  'Unknown' END FROM  TEST,  REQ_COVER, REQ
 WHERE TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID AND REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID ) T    ) F  WHERE Requirements <> 'Unknown' Group by Requirements
) Y , 
(SELECT Requirements, Count(P.Requirements) As 'TotalRequirements',
Count(case when P.TestCoverage > 0 Then P.TestCoverage End) As 'Requirementswithcoverage' , Count(case when P.TestCoverage = 0 Then P.TestCoverage End) As 'Requirementswithnocoverage' , Sum(P.TestCaseValidated) AS 'Test Case validated' FROM ( SELECT REQ.RQ_REQ_ID, REQ.RQ_REQ_NAME, COUNT(REQ_COVER.RC_ITEM_ID) AS TestCoverage ,  (SELECT COUNT(CASE WHEN TEST.TS_USER_24  <> '' Then TEST.TS_USER_24 END) FROM TEST, REQ_COVER WHERE REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID AND REQ.RQ_REQ_ID  = REQ_COVER.RC_REQ_ID )AS TestCaseValidated,
Requirements = Case
   WHEN RQ_REQ_PATH Like 'AAAAADAARAADAAC%' Then 'Medicare'
   WHEN RQ_REQ_PATH Like 'AAAAADAALAAN%' THEN 'MHSA'
   Else 'Unknown' END FROM  REQ  LEFT JOIN REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID WHERE REQ.RQ_TYPE_ID IN (3,4)
GROUP BY REQ.RQ_REQ_NAME, REQ.RQ_REQ_ID, REQ.RQ_REQ_PATH, REQ.RQ_REQ_AUTHOR
) P WHERE Requirements <> 'Unknown' Group by Requirements ) Z
WHERE Y.Requirements = Z.Requirements