COUNT()回傳分組表中的總行數

我有兩個表：

作業表：

[![在此處輸入影像描述][1]][1]

FailedReason 表通過以下方式參考到 Job 表：

[![在此處輸入影像描述][2]][2]

我的目標是根據故障原因計算故障率。

我的期望是得到結果，第一列包含failure reason name，第二列包含total number of all jobs('successful' 'failed')，第三列包含total number of failed jobs由原因引起的，第四列包含failure ratio使用以下公式計算的結果: 失敗計數（3 列）/總計數（2 列）* 100。

我的 sql 查詢：

SELECT
  FailedReason.main_reason as "Failure reason",
  COUNT(job.name) AS "Total jobs",
  SUM(CASE WHEN job.status='failed' THEN 1 ELSE 0 END) AS "Total failed jobs",
  SUM(CASE WHEN job.status='failed' THEN 1 ELSE 0 END) / COUNT(job.name) * 100 AS "Failure ratio"
FROM job
LEFT JOIN FailedReason
ON job.id=FailedReason.job_id
GROUP BY 1
ORDER BY 3 DESC

我得到了計算匯總表中作業總數的結果。結果，故障率是百分之一百。

[![在此處輸入影像描述][3]][3]

我應該修改什么才能獲得正確的作業數量（“成功” “失敗”）并計算正確的比率值

樣本資料：

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');



  [1]: https://i.stack.imgur.com/CYnPg.png
  [2]: https://i.stack.imgur.com/t3baS.png
  [3]: https://i.stack.imgur.com/LC7Hp.png

uj5u.com熱心網友回復：

使用SUM代替COUNT

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