Oracle – Sub-Query GROUP BY COUNT(*) Not Returning 0

group byoracleoracle-12csubquery

Prefix: I am working on an application that is tracking employee training time and I need to get the total number of employees that have reached 75 total hours of training. After researching for some time I have determined I should probably use a join clause but joins are not my strength.

Question: For the following query I am having a problem getting the 'completed' subquery to return a 0 value.

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
;

Thanks for any help.

Best Answer

You don't need a join. First you need to GROUP BY employee, in order to get the hours per employee. Assuming you have an employee_id that identifies employees:

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

Then you need to do another GROUP BY, in the whole result set, to count the number of employees that have over and under 75 hours:

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
 ;

You could also use window functions and not use subqueries but the code is rather obfuscated:

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 ;
Related Question