title: SQL Order By Within A Count(Distinct)
body: <p>I have the following tables:</p>
  
  <pre><code>filetype1
  F1_ID | F1_ORDR | FILENAME
  1     | 1        | file1.txt
  2     | 2        | file2.txt
  3     | 3        | file3.txt
  4     | 2        | file4.txt
  5     | 4        | file5.txt
  
  filetype2
  F2_ID | F2_ORDR | FILENAME
  1     | 1        | file6.txt
  2     | 2        | file7.txt
  3     | 4        | file8.txt
  
  ordr
  OR_ID | OR_VENDOR
  1     | 1
  2     | 1
  3     | 1
  4     | 1
  
  vendor
  VE_ID | VE_NAME
  1     | Company1
  </code></pre>
  
  <p>My goal is to have a list of vendors and a count of the number of orders where a file is connected for each type. For example, the end result of this data should be:</p>
  
  <pre><code>VENDOR   | OR_CT | F1_CT | F2_CT
  Company1 | 4        | 4        | 3
  </code></pre>
  
  <p>Because at least 1 type1 file was attached to 4 distinct orders and at least 1 type2 file was attached to 3 distinct orders. Currently my SQL code looks like this:</p>
  
  <pre><code>SELECT vendor.ve_id, vendor.ve_name,
         (SELECT COUNT(or_id)
          FROM ordr
          WHERE ordr.or_vendor = vendor.ve_id) as OR_COUNT, 
         (SELECT COUNT(DISTINCT f1_order)
          FROM filetype1 INNER JOIN ordr ON filetype1.f1_ordr = ordr.or_id
          WHERE ordr.or_vendor = vendor.ve_id) as F1_CT,
         (SELECT COUNT(DISTINCT f2_ordr)
          FROM filetype2 INNER JOIN ordr ON filetype2.f2_ordr = ordr.or_id
          WHERE ordr.or_vendor = vendor.ve_id) as F2_CT
  FROM vendor
  ORDER BY vendor.ve_name
  </code></pre>
  
  <p>Unfortunately this yields the following results:</p>
  
  <pre><code>VENDOR   | OR_COUNT | F1_COUNT | F2_COUNT
  Company1 | 4        | 5        | 3
  </code></pre>
  
  <p>My only guess is that because I'm using <code>COUNT(DISTINCT)</code> the <code>COUNT</code> is automatically assuming the <code>DISTINCT</code> is ordering by <code>F1_ID</code> instead of by <code>F1_ORDR</code></p>
  
  <p>If anyone can assist me on how to tell the <code>COUNT(DISTINCT)</code> to order by <code>F1_ORDR</code> that would be most helpful. I have searched the vast internet for a solution but its hard to explain what I want to a search engine, forums, etc. My database uses Microsoft SQL Server. My knowledge of database management is almost completely self taught, so I'm just glad I made it this far on my own. My expertise is in web design. Thank you for your time.</p>