title: Selecting Top Seller from Dealers Table with count of sales
body: <p>First off I have Agents and each Agent can have dealer(s).  I have an agent table containing all of my agents.  I have a contracts table where all of the sales are listed.  And I have a dealer table that attaches all of the dealers to agents.</p>
  
  <pre><code>SELECT a.agent_name, COUNT(distinct c.dealer_number) as '# of Selling Dealers'
  FROM agents a
  inner join contracts c on c.agent_number = a.agent_number
  inner join dealers d on d.agent_number = a.agent_number 
  group by agent_name
  </code></pre>
  
  <p>For example this query if I ran it right now would return</p>
  
  <pre><code>Agent Name     # of selling Dealers
  Agent #1            6
  </code></pre>
  
  <p>What I now need to figure out is who is the top seller and how much they sold.  This info would be in the contracts table.</p>
  
  <p>So as in the example <code>Agent #1</code> has 6 dealers who have made sales.  Lets pretend <code>Dealer #5</code> of that list has the most sales.</p>
  
  <p>So it should return as follows:</p>
  
  <pre><code>Agent Name     # of selling Dealers      Top Dealer      Volume
  Agent #1            6                     Dealer #5        24
  </code></pre>
  
  <p>I would like to return the dealer_name of the top seller and how many sales they got (also in the contracts table).</p>
  
  <p><strong>Additional Info</strong></p>
  
  <p>Each line in the contracts table counts as a sale.  It will have a <code>dealer_name</code>, <code>dealer_number</code> and <code>agent_name</code>, <code>agent_number</code></p>
  
  <p>agents table has agent_name, agent_number
  dealers table has dealer_name, dealer_number, agent_number
  contracts table has agent_number, dealer_number</p>