I have a table :

Person :

(integer) (varchar)  (Date)         (date)
  id       state       d1            d2  
  1       'ALIVE'   '2015-08-07'  '2015-08-08'
  2       'DEAD'    '2015-08-08'  '2015-08-10'
  3       'DEAD'    '2015-08-08'  '2015-08-08' 

And so on..

What I want is the count of all people when :

  1. person is alive and d1 <=someDate
  2. person is dead and d2 > someDate and d1 < = someDate.

So, in the above case, if someDate = '2015-08-08', then the output should be 2. i.e, id 3 should not be considered.

I can of course do this as 2 seperate queries and add the result. How to do this in just one query.

Note : This is an example of what I am trying to do. The table names, columns are different in my code.


That's a simple OR condition:

select *
from person
where (state = 'ALIVE' and d1 <= DATE '2015-08-08')
   or (state = 'DEAD' and d2 > DATE '2015-08-08');

The parentheses combining the AND operators are required, otherwise you'd get a wrong result.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.