테스트 케이스의 성공 건수를 해당 테스트 액티비티가 모두 성공했을 때로 정의하기 위해, 각 테스트 케이스에 연결된 모든 테스트 액티비티의 verification_result가 'S'일 경우에만 해당 케이스를 성공으로 간주

SELECT 
    tm.id AS master_id,
    tm.name AS master_name,
    COUNT(DISTINCT ts.id) AS scenario_count,
    COUNT(DISTINCT tc.id) AS case_count,
    COUNT(DISTINCT ta.id) AS activity_count,
    COUNT(DISTINCT CASE WHEN ta.verification_result = 'S' THEN ts.id END) AS successful_scenarios,
    COUNT(DISTINCT CASE 
        WHEN tc.id IS NOT NULL AND 
             NOT EXISTS (
                 SELECT 1 
                 FROM TestActivity ta2 
                 WHERE ta2.case_id = tc.id AND ta2.verification_result <> 'S'
             ) 
        THEN tc.id 
    END) AS successful_cases,
    COUNT(DISTINCT CASE WHEN ta.verification_result = 'S' THEN ta.id END) AS successful_activities
FROM 
    TestExecutionMaster tm
LEFT JOIN 
    TestScenario ts ON tm.id = ts.master_id
LEFT JOIN 
    TestCase tc ON ts.id = tc.scenario_id
LEFT JOIN 
    TestActivity ta ON tc.id = ta.case_id
GROUP BY 
    tm.id, tm.name
ORDER BY 
    tm.id;

 

SELECT 
    tm.id AS master_id,
    tm.name AS master_name,
    COUNT(DISTINCT ts.id) AS scenario_count,
    COUNT(DISTINCT tc.id) AS case_count,
    COUNT(DISTINCT ta.id) AS activity_count,
    COUNT(DISTINCT CASE WHEN ta.verification_result = 'S' THEN ts.id END) AS successful_scenarios,
    COUNT(DISTINCT CASE WHEN tc.id IS NOT NULL AND ta.verification_result = 'S' THEN tc.id END) AS successful_cases,
    COUNT(DISTINCT CASE WHEN ta.verification_result = 'S' THEN ta.id END) AS successful_activities
FROM 
    TestExecutionMaster tm
LEFT JOIN 
    TestScenario ts ON tm.id = ts.master_id
LEFT JOIN 
    TestCase tc ON ts.id = tc.scenario_id
LEFT JOIN 
    TestActivity ta ON tc.id = ta.case_id
GROUP BY 
    tm.id, tm.name
ORDER BY 
    tm.id;