Filter on value in another row

firebirdgroup byselect

Please see image of the data and my code so far.

In this example I only want the first two rows to be retrieved. The reason for this is because where JobID is 1684 the sum of STILL_REQUIRED is > 0

Where JOBID is 1683 the sum of STILL_REQUIRED is 0, so don't show this row.

I think I need to get the sum when it is grouped by JobID, but I cant get it working. I am using Firebird.

enter image description here

enter image description here

Here is the code I have so far:

SELECT * 
FROM (SELECT ID, 
             JobID, 
             NAME, 
             ORDERNUM, 
             STARTAFTER, 
             FINISHBEFORE, 
             START_OFFSET_MINS, 
             TOTAL_MINUTES_REQ - 
             (SELECT COALESCE(
                       SUM(TABLE_PROCESSTIMES.END_TIME - 
                           TABLE_PROCESSTIMES.START_TIME) / 60, 0) 
                FROM TABLE_PROCESSTIMES 
               WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID 
                  AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
             ) AS STILL_REQUIRED 
FROM TABLE_PROCESSES) 
WHERE STILL_REQUIRED > 0

Best Answer

Based on my understanding of your needs, you could use a Common Table Expression (CTE) to achieve this. Define your main query within the CTE and then do a JOIN on a second SELECT which calculates the total per JobID:

WITH results AS
(
    SELECT ID, 
           JobID, 
           NAME, 
           ORDERNUM, 
           STARTAFTER, 
           FINISHBEFORE, 
           START_OFFSET_MINS, 
           TOTAL_MINUTES_REQ - 
           (SELECT COALESCE(
                     SUM(TABLE_PROCESSTIMES.END_TIME - 
                         TABLE_PROCESSTIMES.START_TIME) / 60, 0) 
              FROM TABLE_PROCESSTIMES 
             WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID 
               AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
           ) AS STILL_REQUIRED 
      FROM TABLE_PROCESSES
)
SELECT x.*
  FROM results x
  JOIN (SELECT JobID, SUM(STILL_REQUIRED) AS STILL_REQUIRED
          FROM results
      GROUP BY JobID) y
    ON x.JobID = y.JobID
 WHERE y.STILL_REQUIRED > 0
Related Question