子查詢分組計數（*）不返回 0

**前綴：**我正在開發一個跟踪員工培訓時間的應用程序，我需要獲取已達到 75 總培訓小時的員工總數。經過一段時間的研究，我確定我可能應該使用連接子句，但連接不是我的強項。

**問題：**對於以下查詢，我在獲取“已完成”子查詢以返回 0 值時遇到問題。

SELECT
   Completed.Total_Complete "Completed Training",
   Incomplete.Total_Incomplete "Incompleted Training"
FROM

   (
    SELECT 
       count(*) AS Total_Complete
    FROM 
       EMPLOYEE_TRAINING_RECORD
    HAVING SUM(TRAINING_HOURS) >= 75
   )
   Completed,

   (
    SELECT 
       count(*) AS Total_Incomplete
    FROM 
       EMPLOYEE_TRAINING_RECORD
    HAVING SUM(TRAINING_HOURS) < 75
   )
   Incomplete
;

謝謝你的幫助。

你不需要加入。首先，您需要GROUP BY員工，以獲得每位員工的工作時間。假設您有一個employee_id標識員工的：

SELECT
    employee_id, 
    SUM(TRAINING_HOURS) AS Hours_Worked
FROM 
    EMPLOYEE_TRAINING_RECORD
GROUP BY 
    employee_id ;

然後你需要在整個結果集中再做一次GROUP BY，計算超過和低於 75 小時的員工數量：

SELECT
  COUNT(CASE WHEN Hours_Worked >= 75 THEN 1 END) AS Completed_Training,
  COUNT(CASE WHEN Hours_Worked  < 75 THEN 1 END) AS Incomplete_Training
FROM
  (  SELECT
        -- employee_id,      -- we don't need that in the final results 
        SUM(TRAINING_HOURS) AS Hours_Worked
     FROM 
        EMPLOYEE_TRAINING_RECORD
     GROUP BY 
        employee_id
  ) grp                   -- the first group result set
;

您也可以使用視窗函式而不使用子查詢，但程式碼相當模糊：

SELECT DISTINCT 
  COUNT(CASE WHEN SUM(TRAINING_HOURS) >= 75 THEN 1 END) 
     OVER () AS Completed_Training,
  COUNT(CASE WHEN SUM(TRAINING_HOURS)  < 75 THEN 1 END)
     OVER () AS Incomplete_Training
FROM
  EMPLOYEE_TRAINING_RECORD
GROUP BY 
  employee_id ;

引用自：https://dba.stackexchange.com/questions/150518