0

I have a database table called JOBS which has ID,STATUS and COMPLETION_TIME as the columns. STATUS 0,1 is success, STATUS 2,3 is fail and STATUS 4,5 is killed. It can have only one status. I want to find number of jobs that succeeded , failed and killed within last 10 hours. I have to use only SELECT for the query and cannot any variables for the query. I used following query for the operation. 

SELECT
(SELECT COUNT(*) 
WHERE STATUS IN (0,1))AS SuccessCount, 

(SELECT COUNT(*)
WHERE STATUS IN (2,3))AS FailedCount , 

(SELECT COUNT(*)
WHERE STATUS IN (4,5))AS KilledCount,

FROM JOBS 

WHERE COMPLETION_TIME >= (SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) - (10 * 60 * 60))
AND COMPLETION_TIME <= (SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()));

But this query is not working. It is giving me jobs which succeeded, failed or killed and not their number.

msd_2
  • 1,117
  • 3
  • 16
  • 22
  • I think that you expect subquery to access all the rows from outer query. This is not true. Subquery can use columns from current row only. For this to work, you would have to repeat `from jobs where ...` in each subquery and remove from ... where from outer query. Better way is, of course, presented in Gordon Linoff's answer, but he is missing `FROM JOBS`. – Nikola Markovinović Jul 27 '12 at 19:48
  • It is possible for me to repeat 'from jobs where...' in each sub-query, but as I am dealing here with start time and end time, wouldn't repeating the time part in where clause for three sub-query have different results, as the instance at which time will be noted will be slightly different? – msd_2 Jul 30 '12 at 14:47
  • You don't need to do it this way, please check @GordonLinoff's answer for perfectly valid way to conditionally sum records. Getutcdate() (and other functions) seem not to be cached for the query, so yes, it is possible to get different results. Please check [this link](http://stackoverflow.com/questions/6036223/will-getutcdate-return-the-same-value-if-used-twice-in-the-same-statement) for detailed tests. – Nikola Markovinović Jul 30 '12 at 19:45

1 Answers1

0

Try this:

select sum(case when status in (0, 1) then 1 else 0 end) as succeeded,
       sum(case when status in (2, 3) then 1 else 0 end) as failed,
       sum(case when status in (4, 5) then 1 else 0 end) as killed
from jobs
WHERE COMPLETION_TIME >= (SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) - (10 * 60 * 60)) and
      COMPLETION_TIME <= (SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()));

Your original query is not syntactically correct. I don't see how it is returning any data, since the subquery:

(SELECT COUNT(*) WHERE STATUS IN (0,1))

Has not "FROM" clause to define the variable status.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786