By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE job (
id INT PRIMARY KEY,
created_at date,
finished_at date,
status varchar(255)
);
CREATE TABLE failedreason (
id INT PRIMARY KEY AUTO_INCREMENT,
job_id INT REFERENCES job(id),
main_reason varchar(255)
);
INSERT INTO job
(id, created_at , finished_at , status) VALUES
(13095427, '2021-05-03 02:50:41', '2021-05-03 03:47:27', 'failed')
, (13095407, '2021-05-03 02:50:39', '2021-05-03 03:46:41', 'failed')
, (13095533, '2021-05-03 02:50:41', '2021-05-03 03:47:27', 'failed')
, (13095546, '2021-05-03 02:50:41', '2021-05-03 03:47:27', 'failed')
, (13098367, '2021-05-03 02:50:41', '2021-05-03 03:47:27', 'failed')
, (13101522, '2021-05-03 02:50:41', '2021-05-03 03:47:27', 'failed')
, (13101444, '2021-05-03 02:50:41', '2021-05-03 03:47:27', 'success')
, (13101445, '2021-05-03 02:50:41', '2021-05-03 03:47:27', 'success')
, (13101446, '2021-05-03 02:50:41', '2021-05-03 03:47:27', 'success')
;
INSERT INTO failedreason
(job_id, main_reason) VALUES
(13095427, 'test case failure')
, (13095407, 'test case failure')
, (13095533, 'connection error')
, (13095546, 'connection error')
, (13098367, 'runner connection error')
, (13101522, 'script error')
;
SELECT
CASE WHEN GROUPING(job.status) = 1 THEN 'TOTAL' ELSE job.status END AS `Job Status`
, CASE WHEN GROUPING(fail.main_reason) = 1 THEN 'TOTAL' ELSE fail.main_reason END AS `Failure Reason`
, COUNT(DISTINCT job.id) AS `Total Jobs`
, COUNT(DISTINCT job.id) / MAX(tots.total_jobs) * 100 AS `Ratio`
FROM job
CROSS JOIN (SELECT COUNT(*) AS total_jobs FROM job) tots
LEFT JOIN failedreason fail
ON job.id = fail.job_id
GROUP BY job.status, fail.main_reason WITH ROLLUP
ORDER BY GROUPING(job.status), GROUPING(fail.main_reason), `Ratio` DESC;
| Job Status | Failure Reason | Total Jobs | Ratio |
|---|---|---|---|
| success | null | 3 | 33.3333 |
| failed | connection error | 2 | 22.2222 |
| failed | test case failure | 2 | 22.2222 |
| failed | runner connection error | 1 | 11.1111 |
| failed | script error | 1 | 11.1111 |
| failed | TOTAL | 6 | 66.6667 |
| success | TOTAL | 3 | 33.3333 |
| TOTAL | TOTAL | 9 | 100.0000 |