test_cases_run_status

CREATE VIEW test_cases_run_status AS 
SELECT 
    g.name AS group_name,
    g.suite_id,
    g.id AS group_id,
    g.created_by,   
    strftime('%d-%m-%Y',  g.created_at) AS formatted_test_case_created_at,
    COUNT(tc.test_case_id) AS test_case_count,
    COUNT(p.test_case_id) AS success_status_count,
    (COUNT(tc.test_case_id)-COUNT(p.test_case_id)) AS failed_status_count
FROM groups g
LEFT JOIN test_cases tc
    ON g.id = tc.group_id
LEFT JOIN test_case_run_results p on p.test_case_id=tc.test_case_id and status='passed'
GROUP BY g.name, g.id