title: How can we filter subquery in oracle?
body: <p>The query I am using is</p>
  
  <pre><code>select count(*) as count,
         TO_CHAR(firstTable1.firstTable_date, 'DD-MON-YY') as  dateAndTime
  from  (
    select *
    from   firstTable
    where  firstTable_status = 'Open'
    and    firstTable_date   &gt; sysdate-10
  ) firstTable1
  group by TO_CHAR(firstTable1.firstTable_date, 'DD-MON-YY')
  </code></pre>
  
  <p>which will give me count 1 on 11-may-17</p>
  
  <p>and count 1 for 09-may-17</p>
  
  <p>sample table data is</p>
  
  <p>firstTable</p>
  
  <pre><code>                    id        firstTable_status  firstTable_date
  
                     A-123         Open                11-may-17
                     B-123         Open                09-may-17
  </code></pre>
  
  <p>secondTable</p>
  
  <pre><code>  secondTable_Id    firstTable_id      secondTable_Action  secondTable_date
  
         XX1       A-123            started                 11-may-17
         XX2       A-123            Assigned                11-may-17
         XX3       A-123            action2                 11-may-17
         XX4       A-123            action3                 11-may-17
         XX5       A-123            action4                 11-may-17
         XX6       A-123            action5                 11-may-17
         XX7       A-123            closed                  11-may-17
  
         MN1       B-123            started                09-may-17
         MN1       B-123            action1                10-may-17
         MN1       B-123            closed                 11-may-17
  </code></pre>
  
  <p>is it possible to modify this query such as </p>
  
  <p>if the secondTable_Action is closed for same id on same date then then query should not return count for that id</p>
  
  <p>for example  id A-123 in firstTable is open on 11-may-17 and closed on same 
      day whose entry in second table for this query should return count 0  for 11-
      may-17
      while for id B-123 is closed on different day so for this it should return 
      count 1 on 09-may-17</p>