Stumped on trying to add 2 count subqueries to main query in oracle

See more:

SQL

Oracle

I am try to update an existing query to include 2 subqueries each computing a different count. The main query runs fine on its own and I have been able to get the 2 subqueries to run on their own, however, when I try to join them, I am getting a syntax error "missing keyword".

Here is my main query. It takes the inspection data from 3 separate tables and summarizes it by the job number to show each time the inspection program was ran. The inspection data is split up into the different tables based on the feature type (holes, zaxis or corner). The results for each job number can be in found in just 1 of these 3 tables or multiple tables. Because of this, that is why there is a MIN function in the main query so I end up with one entry per job number. Another thing to note is each table can then have one or more probe features of the same type with the difference being in a different location.

SELECT MIN(t.timeofentry) as timeofentry, t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber 
from (
     SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONHOLE
      WHERE HOLENUMBER = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONZAXIS
      WHERE ZHIT = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONCORNER
      WHERE TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      )t 
      GROUP BY t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber
      ORDER BY timeofentry

This returns data like:
TimeofEntry, Machine, Tape, PartNumber, Spindle, JobNumber
01/02/2024 11:13:50 PM, NC-537, 4479, ABC, LH, 75576
01/02/2024 11:25:50 PM, NC-537, 4479, ABC, RH, 75577

Now I want to add 2 counts to the query to show both the total number of features inspected for the job number and the total number of features that were out of tolerance for that job number. Something like this:

TimeofEntry, Machine, Tape, PartNumber, Spindle, JobNumber, Failed, Total
01/02/2024 11:13:50 PM, NC-537, 4479, ABC, LH, 75576, 0, 4
01/02/2024 11:25:50 PM, NC-537, 4479, ABC, RH, 75577, 2, 4

Here are the subqueries for each. On the one that includes the failed features, I think I will need a COALESCE in there somewhere as not every job has failed features so the subquery may return null sometimes

This one includes the number of failed features:

select jobnumber, sum(fails)as TotalFails
from(
select jobnumber, count(*) as fails from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and (xoutoftol = 'Yes' or youtoftol = 'Yes') group by jobnumber
union
select jobnumber, count(*) as fails from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and Zoutoftol = 'Yes' group by jobnumber
union
select jobnumber, count(*) as fails from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and (xoutoftol = 'Yes' or youtoftol = 'Yes') group by jobnumber)
group by jobnumber

This one includes the total number of features:

select jobnumber, sum(features)as TotalFeatures
from(
select jobnumber, count(*) as features from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
union
select jobnumber, count(*) as features from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
union
select jobnumber, count(*) as features from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber)
group by jobnumber

What I have tried:

Here is what I was trying when I got the syntax error "missing keyword". I was just trying to add the total features first and then do the failed features but I can't seem to get just the total features to work.

SELECT MIN(t.timeofentry) as timeofentry, t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber, TF.totalfeatures
from (
     SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONHOLE
      WHERE HOLENUMBER = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONZAXIS
      WHERE ZHIT = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONCORNER
      WHERE TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      )t
      left join
      (select jobnumber, sum(features)as TotalFeatures
      from(
      select jobnumber, count(*) as features from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
      union
      select jobnumber, count(*) as features from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
      union
      select jobnumber, count(*) as features from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber)
      group by jobnumber) as TF
      on t.jobnumber = TF.jobnumber
      GROUP BY t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber, tf.totalfeatures
      ORDER BY timeofentry