title: Oracle compare two count() different tables
body: <p>I have a problem with a query, see I have two tables, let say:</p>
  
  <pre><code>table a:
  progid     |    name  |   type
  12         |    john  |    b
  12         |    anna  |    c
  13         |    sara  |    b
  13         |    ben   |    c
  14         |    alan  |    b
  15         |    george|    b
  
  table b:
  progid     |    name  |   type
  12         |    john  |    b
  12         |    anna  |    c
  13         |    sara  |    b
  14         |    alan  |    b
  15         |    george|    b
  </code></pre>
  
  <p>table a gets count</p>
  
  <pre><code>progid   | count(*)
  12       | 2
  13       | 2
  14       | 1
  15       | 1
  </code></pre>
  
  <p>table b gets</p>
  
  <pre><code>progid   | count(*)
  12       | 2
  **13     | 1**&lt;-this is what I want to find different count
  14       | 1
  15       | 1
  </code></pre>
  
  <p>What I want is to find which progid in table b aren't in table a by count, (because as you can see the prog id is there but they should be there the same times! So ben is gone but the progid 13 is there)</p>
  
  <p>So I want to get progid where count varies in the tables, I tried:</p>
  
  <pre><code>select a.progid from 
  (select progid ,count(*) total from tablea group by progid) a,
  (select progid ,count(*) total from tableb group by progid) b 
  where
  a.progid=b.progid and a.total&lt;&gt;b.total;
  </code></pre>
  
  <p>I get b.total invalid identifier</p>
  
  <pre><code>if I use a.count(progid)&lt;&gt;b.count(progid)
  </code></pre>
  
  <p>Error says can't use group functions there, any ideas? I'm desperate! </p>
  
  <hr>
  
  <p>ok i've checked your answers and here's the original one </p>
  
  <pre><code>select a.beneficiarioid from 
  (select beneficiarioid,count(*) total from lmml_ejercicio_2012_3 where programaid=61 group by beneficiarioid order by beneficiarioid) a,
  (select beneficiarioid,count(*) total from ejercicio_2012_3 where programaid=61 group by beneficiarioid order by beneficiarioid) where
  a.beneficiarioid=b.beneficiarioid and a.total&lt;&gt;b.total;
  </code></pre>
  
  <p>anyway, i'll try your querys and let you know!! thank you very much!!
  btw it's Oracle 11g</p>