title: max(count) oracle
body: <p>I want to select the employee who has signed most contracts in the last 3 months:
  The connections between tables are: contracts.responsible references employees(id) </p>
  
  <pre><code>select b.name, b.id, count(*) nr_contracts_last_3_mths
      from contracts a join employees b
      on a.responsible = b.id and   
      data between add_months(trunc(sysdate, 'MM'), -1) and trunc(sysdate)
      group by b.name, b.id
      having count(*) = 
        (select max(count(responsible)) 
              from contracts, employees
                where contracts.responsible = employees.id
                group by employees.name
          );
  </code></pre>
  
  <p>The result is contains only the empty selected columns (no data). </p>
  
  <p>The expected result should have <strong>the name of the responible</strong>, <strong>his id</strong>, and value <strong>5</strong> since the bellow query returns 5;</p>
  
  <pre><code>select max(count(*)) nr_contracts_last_3_mths from contracts where data 
         between add_months(trunc(sysdate, 'MM'), -3) and trunc(sysdate)
         group by data;
  </code></pre>