title: Oracle SQL Count Children and Parents by Date and ID
body: <p><strong>Background:</strong> I need a way to count the number of records that we will be running through our Archive process. The query I have below what I have come up with but I feel like there has to be a better way (Takes about 20minutes to run for the last 90 days)</p>
  
  <p><strong>The Query</strong>: We have a table called Table_Main (Not its real name) which contains alot of records (300 million +) and is constantly being added to (Hence the archiving). The table contains parent records that have a TR_ID, which links them to another table, while the children do not have a TR_ID. The children are attached to the parents via a Parent_ID. The tricky part here is that I need to count the records based of values contained in the Trans table which is linked via TR_ID. </p>
  
  <pre><code> Select  B.Some_date, B.xx_ID, count(*)
  From
  (
  select tr.Some_date, tr.xx_ID, p.H_ID
  from Table_Main p, Trans tr
  where p.TR_ID = tr.TR_ID 
   and tr.Some_date&gt;sysdate-90
       --
  UNION ALL
          --
  Select Result.Some_date , Result.xx_ID, th.H_ID
  from (
  select tr.Some_date, tr.xx_ID, p.H_ID
  from Table_Main p, Trans tr
  where p.TR_ID = tr.TR_ID  and tr.Some_date&gt;sysdate-90
  ) Result
    inner join Table_Main th on th.Parent_id = Result.H_ID and th.Parent_ID is not null
    ) B
    group by  B.Some_date, B.xx_ID
  order by B.Some_date;
  </code></pre>
  
  <p><strong>Question/Idea</strong>: Is there any way this could be simplified with one join between Table_Main and itself? For example a join that keeps the first record from the parent plus all the joined children? I was trying something like the query below but not getting anywhere.</p>
  
  <pre><code>Select a.Some_date, A.xx_ID, p.H_ID, c.*
  from Table_Main p
  inner join Trans a on p.TR_ID = a.TR_ID
  left join Table_Main c on c.Parent_id = p.H_ID 
  where a.Some_date&gt; sysdate-20
  order by p.H_ID, c.H_ID
  </code></pre>
  
  <p>Table_Main</p>
  
  <pre><code>-H_ID
  -Parent_ID //Links child to parent
  -TR_ID     //Links to Trans Table
  </code></pre>
  
  <p>Trans</p>
  
  <pre><code>-TR_ID     //Link to Table_Main
  -xx_ID     //This is used to group on
  -Some_Date //Used to group on
  </code></pre>
  
  <p><strong>Sample Input:</strong></p>
  
  <p>Table_Main</p>
  
  <pre><code>H_ID     Parent_ID     TR_ID
  1        NULL          1
  2        1             NULL
  3        NULL          2
  4        NULL          3
  5        4             NULL
  6        4             NULL
  7        NULL          4
  8        7             NULL
  9        NULL          5
  10       9             NULL
  11       9             NULL
  12       9             NULL
  13       9             NULL
  14       9             NULL
  15       9             NULL
  16       9             NULL
  </code></pre>
  
  <p>Trans</p>
  
  <pre><code>TR_ID    XX_ID    Some_Date
  1        45       12/1/2015
  2        4        12/1/2015
  3        6        12/20/2015
  4        45       12/1/2015
  5        23       12/22/2015
  </code></pre>
  
  <p>Desired Output:</p>
  
  <pre><code>Date      xx_ID Count
  12/1/2015   4   1
  12/1/2015   45  4
  12/20/2015  6   3
  12/22/2015  23  8
  </code></pre>
  
  <p>Thanks in advance for any help you can provide.</p>