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(CASE WHEN tc.test_status = 'passed' THEN tc.test_case_id END) AS success_status_count,
    COUNT(CASE WHEN tc.test_status = 'failed' THEN tc.test_case_id END) AS failed_status_count,
    COUNT(CASE WHEN tc.test_status is null THEN tc.test_case_id END) AS todo_status_count
    FROM groups g
LEFT JOIN test_cases tc
    ON g.id = tc.group_id
    GROUP BY g.name, g.id