title: oracle query to get count of added, updated and deleted records based on the date filter
body: <p>I have a table structure like below</p>
  
  <pre><code>ID OLDID col1 status 
  1  0     txt1 A      - inserted record      
  2  0     txt1 I      - deleted record
  3  0     txt1 I      - updated record
  4  3     txt1 I      - updated record
  5  4     txt1 I      - updated record
  6  5     txt1 I      - deleted record
  7  0     txtx I      - updated record
  8  7     txt1 A      - updated record
  </code></pre>
  
  <p>I wanted to count the inserted, updated and deleted record in this table, please help me with the oracle query to fetch the count of the records, below are the logic</p>
  
  <ol>
  <li>The logic for new record is ID is new ID and old id is 0 and status is A.</li>
  <li>The updated record's status is I, the id of this record will be inserted into the new record's old id column.</li>
  <li>the deleted record status with I with old id 0, and the second condition is status is I and ID will not be in OLD id column.</li>
  </ol>
  
  <p>I have tried my best to explain the case, please let me know if any more details are needed. help me with the oracle query.</p>
  
  <p>below query seems to be working, but if no deleted record then <strong>I wanted to have 0 for it but in the below query only returns records which have >0 counts</strong>, can any expert optimize it, please</p>
  
  <pre><code>SELECT sub.status, COUNT(*)
  FROM (SELECT CASE 
                 WHEN yt.oldid = 0 AND yt.status = 'A' THEN 'Insert'
                 WHEN yt.status = 'I' and QID not in (select oldid from FROM your_table where TO_CHAR (record_date,'MM/YYYY')='04/2020' )  THEN 'Delete'
                 ELSE 'Update' END AS status
        FROM your_table yt where TO_CHAR (record_date,'MM/YYYY')='04/2020')
  GROUP BY sub.status;
  </code></pre>
  
  <p>Neep experts help here, please.</p>