title: mySQL Query Count Distinct and Percentage
body: <p>I'm having trouble creating a query that will report on training completions by users that are grouped by district within a region.  For one training course the report needs to show for each district </p>
  
  <ul>
  <li>Number of users assigned course</li>
  <li>Number of users completed course</li>
  <li>Percentage of users completed course</li>
  </ul>
  
  <p>The output report should look like this (without the periods).:</p>
  
  <p>District.........Assigned.......Completed....%</p>
  
  <p>Arkansas..........20..............15...............75%</p>
  
  <p>Illinois...............80..............80...............100%</p>
  
  <p>Iowa.................10...............8.................80%</p>
  
  <p>Michigan..........30..............20................66%</p>
  
  <p>Here's the SQL query I have tried using</p>
  
  <pre><code>Select  mytable.district as District,
  
  (Select Count(Distinct mytable.user_id)
       From mytable
       Where mytable.district = District AND
       mytable.training_title = 'My Course') As 'Assigned',
  
  (Select Count(Distinct mytable.user_id) 
       From mytable
       Where mytable.training_status = "Completed" AND
          mytable.district = District AND
          mytable.training_title = 'My Course') as 'Completed',
  
  Concat(Round(100 * (Select Count(Distinct mytable.user_id) 
       From mytable
       Where mytable.training_status = "Completed" AND
       mytable.district = District AND
       mytable.training_title = 'My Course') / (Select Count(Distinct mytable.user_id)
       From mytable
       Where 
          mytable.district = District AND
          mytable.training_title = 'My Course'),0 ),"%") as '%'   
  
  From  mytable  
  Where mytable.region = 'Midwest'
  Group by District
  </code></pre>
  
  <p>It doesn't work at all like this.  However if I substitute one of the district values (such as Arkansas) in for  'District' in the WHERE clause I can get the correct values for that district.  However i need to find all of the districts for each region and calculate the values for district.  This is only one of the regions I need to create the query for.</p>
  
  <p>Two key issues:</p>
  
  <ul>
  <li>All of the data exists in one table in my database.  I have consolidated and imported it from another database.</li>
  <li>The data contains duplicates  Therefore I must use Distinct to eliminate the duplicates from the results.</li>
  </ul>
  
  <p>Suggestions?  Thanks in advance for your assistance!!!</p>