By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE failedreason (id INT PRIMARY KEY AUTO_INCREMENT,
job_id INT REFERENCES job(id),
main_reason varchar(255)
);
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');
CREATE TABLE job (id INT PRIMARY KEY,
created_at date,
finished_at date,
status 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');
select *
from failedreason;
select * from job;
select *
from job j
left join failedreason f on j.id = f.job_id
| id | job_id | main_reason |
|---|---|---|
| 1 | 13095427 | test case failure |
| 2 | 13095407 | test case failure |
| 3 | 13095533 | connection error |
| 4 | 13095546 | connection error |
| 5 | 13098367 | runner connection error |
| 6 | 13101522 | script error |
| id | created_at | finished_at | status |
|---|---|---|---|
| 13095407 | 2021-05-03 | 2021-05-03 | failed |
| 13095427 | 2021-05-03 | 2021-05-03 | failed |
| 13095533 | 2021-05-03 | 2021-05-03 | failed |
| 13095546 | 2021-05-03 | 2021-05-03 | failed |
| 13098367 | 2021-05-03 | 2021-05-03 | failed |
| 13101444 | 2021-05-03 | 2021-05-03 | success |
| 13101445 | 2021-05-03 | 2021-05-03 | success |
| 13101446 | 2021-05-03 | 2021-05-03 | success |
| 13101522 | 2021-05-03 | 2021-05-03 | failed |
| id | created_at | finished_at | status | id | job_id | main_reason |
|---|---|---|---|---|---|---|
| 13095407 | 2021-05-03 | 2021-05-03 | failed | 2 | 13095407 | test case failure |
| 13095427 | 2021-05-03 | 2021-05-03 | failed | 1 | 13095427 | test case failure |
| 13095533 | 2021-05-03 | 2021-05-03 | failed | 3 | 13095533 | connection error |
| 13095546 | 2021-05-03 | 2021-05-03 | failed | 4 | 13095546 | connection error |
| 13098367 | 2021-05-03 | 2021-05-03 | failed | 5 | 13098367 | runner connection error |
| 13101444 | 2021-05-03 | 2021-05-03 | success | null | null | null |
| 13101445 | 2021-05-03 | 2021-05-03 | success | null | null | null |
| 13101446 | 2021-05-03 | 2021-05-03 | success | null | null | null |
| 13101522 | 2021-05-03 | 2021-05-03 | failed | 6 | 13101522 | script error |
select f.main_reason AS "main reason",
COUNT(*) AS "Total jobs",
SUM(CASE WHEN j.status='failed' THEN 1 ELSE 0 END) AS "Total failed jobs",
(SUM(CASE WHEN j.status='failed' THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS "Failure ratio"
from job j
left join failedreason f on j.id = f.job_id
group by f.main_reason
| main reason | Total jobs | Total failed jobs | Failure ratio |
|---|---|---|---|
| test case failure | 2 | 2 | 100.0000 |
| connection error | 2 | 2 | 100.0000 |
| runner connection error | 1 | 1 | 100.0000 |
| null | 3 | 0 | 0.0000 |
| script error | 1 | 1 | 100.0000 |