test_case_related_requirements

Column Type
related_requirement BLOB
suite_id BLOB
suite_name BLOB
test_case_count
passed_count
failed_count

SQL DDL

CREATE VIEW test_case_related_requirements AS
SELECT
    j.value AS related_requirement,
    c.suite_id,
    ts.name AS suite_name,
    COUNT(*) AS test_case_count,
    COUNT(CASE WHEN r.status = 'passed' THEN 1 END) AS passed_count,
    COUNT(CASE WHEN r.status = 'failed' THEN 1 END) AS failed_count
FROM
    test_cases AS c
    -- ✅ Correct usage of json_each() with alias
    , json_each(c.related_requirements) AS j
    JOIN test_suites AS ts ON ts.id = c.suite_id
    LEFT JOIN test_case_run_results AS r
        ON r.test_case_id = c.test_case_id
WHERE
    c.related_requirements IS NOT NULL
GROUP BY
    j.value,
    c.suite_id,
    ts.name
ORDER BY
    c.suite_id,
    related_requirement
;