title: sql - salesmen by territory query - combine multiple statements with WITH clause
body: <p>We have a territory/salesman relationship where: each Territory (TERR) may have multiple salesman (SALM), but each salesman can have only one territory.</p>
  
  <p>Example:</p>
  
  <pre><code>Territory      Salesman   Sales$ Period   
      1             100      999     1
      1             100      999     2
      1             200      999     1 
      1             200      999     2    
      2             300      999     1
      2             300      999     2
      3             400      999     1
      3             400      999     2
  </code></pre>
  
  <p>I would like to build a summary table, preferably using a single SQL statement that -</p>
  
  <ul>
  <li>shows territory,</li>
  <li>number of salesmen for this territory (** CLARIFICATION: ** that is
  total unique salesmen codes that made sales in this territory) and</li>
  <li>list the salesmen numbers in the same file.</li>
  </ul>
  
  <p>example:</p>
  
  <pre><code>Territory # of Salesmen   Salesman   
  --------- -------------   --------   
  1             2            100       
  1             2            200       
  2             1            300       
  3             1            400       
  </code></pre>
  
  <p>SQL:</p>
  
  <pre><code>// First I get a list of territories and number of salesman in each territory
  With tmpFile as (Select TERR, SALM, count(*) 
     FROM FILE
     GROUP BY TERR, SALM 
     ORDER BY TERR, SALM   
  ) 
  SELECT tmpFile.TERR, count(*) FROM tmpFile GROUP BY          
  tmpFile.TERR ORDER BY tmpFile.TERR
  
  // Next step is to get a list of all salesmen in a territory
  Select TERR, SALM
     FROM FILE
     GROUP BY TERR, SALM 
     ORDER BY TERR, SALM
  ) 
  </code></pre>
  
  <p>// Final step would be to combine the above two steps.  I would like to combine all three steps into a single statement if possible.  Is there more effective way to do this.  I am using IBM db/2 SQL.</p>