title: Obtain count for duplicates in two tables oracle sql
body: <p>I have a database and I will primarily focus on two table.  Table 1 has emp_id as primary key</p>
  
  <p>Table 1 store access info for each employee.  I am tasked to count how many time an employee goes into a room..</p>
  
  <p>Table 1</p>
  
  <pre><code>emp_id  time_in time_out, other columns etc
  
  1111    3:00        3.30     
  2222    1:00        1:10
  3333    2:00        2:45
  4444    7:00        5:00
  </code></pre>
  
  <p>table 2</p>
  
  <p>sequence_no, emp_id, time, access type, other columns etc</p>
  
  <p>Table 2 has multiple entries of enties</p>
  
  <pre><code>sequence_no, emp_id, time, access type
  
  10000       1111     3:00   granted
  10221       1111     3:23   granted
  19911       2222      x
  12122       1111      x
  23232       3333
  </code></pre>
  
  <p>I have written sQl query that display joins the two tables,<br>
  but at the moment I am trying to add a column that either sums total entries (due to the sequence number, my query is returning multiple rows)</p>
  
  <pre><code>select e.emp_id,a.sequence_no,count(sequence_no) from employee, access a where e.emp_id = a.emp_id 
  group by e.emp_id having count t(1) &gt; 1
  </code></pre>
  
  <p>output should look like</p>
  
  <pre><code>emp_id, sequence number, time in/out , total_counts
  
  1111    10000              3:30         5
  1111    12122              3:30         5
  2222    19911              2:20         19
  </code></pre>
  
  <p>within the results, I need the sequence number which will cause duplicate emp_id, but the total for each ID should be the same accross;</p>