title: How to use Condition in oracle count Function
body: <p>I have two table <code>Area_Table</code> which has a column name as Area.ID and <code>Defect_table</code> which has a column name as Area.Id, Defect_date.</p>
  
  <p>I need to display <code>Area.ID</code> and "Number of Defects" by a "count of Defect_date" also it should only display dates-</p>
  
  <ol>
  <li>Which are within Last Friday to before Friday in one column</li>
  <li>Dates which falls between 4 Fridays.</li>
  </ol>
  
  <p>Sample Table :</p>
  
  <pre><code>    Area_Table -
  
      Area_ID
      ABC1
      BCD2
      EFG4
  </code></pre>
  
  <hr>
  
  <p>Defect_table</p>
  
  <pre><code>Area_Id      Defect_date
  ABC1         13/03/2018
  ABC1         11/03/2018
  EFG4         08/03/2018
  </code></pre>
  
  <hr>
  
  <p>Required output -</p>
  
  <pre><code>Area_id    Count of 1 week     Count of 4week
  ABC1              2                    2
  BCD2              0                    0
  EFG4              0                    1
  </code></pre>
  
  <p>Since today is 23/03/2018(Friday) - </p>
  
  <p>Last week Friday range is 09/03/2018 to 16/03/2018.
  4 weeks range is 23/03/2018 to 16/03/2018.</p>
  
  <p>My code -</p>
  
  <pre><code>Select A.Area_Id, D.Defect_date, 
  count((next_day(trunc(sysdate, 'iw'), 'Friday') - 14-(next_day(trunc(sysdate, 'iw'), 'Friday') -  7)) as "Count of 1 week", 
  count((next_day(trunc(sysdate, 'iw'), 'Friday') - 28 -(next_day(trunc(sysdate, 'iw'), 'Friday') -  7)) as "Count of 4 week" 
  From Area_table A inner join Defect_date D on A.Area_ID = D.Area_ID
  Group by A.Area_Id, D.Defect_date;
  </code></pre>
  
  <p>This code shows the wrong output! which is not matching to my required output table above.</p>