title: MySQL SELECT Multiple DISTINCT COUNT
body: <p>Here is what I'm trying to do. I have a table with user assessments which may contain duplicate rows. I'm looking to only get <code>DISTINCT</code> values for each user.</p>
  
  <p>In the example of the table below. If only user_id 1 and 50 belongs to the specific location, then only the unique video_id's for each user should be returned as the <code>COUNT</code>. User 1 passed video 1, 2, and 1. So that should only be 2 records, and user 50 passed video 2. So the total for this location would be 3. I think I need to have two <code>DISTINCT's</code> in the query, but am not sure how to do this.</p>
  
  <pre><code>+-----+----------+----------+
  | id  | video_id | user_id  |
  +-----+----------+----------+
  | 1   |       1  |        1 |
  | 2   |       2  |       50 |
  | 3   |       1  |      115 |
  | 4   |       2  |       25 |
  | 5   |       2  |        1 |
  | 6   |       6  |       98 |
  | 7   |       1  |        1 |
  +-----+----------+----------+
  </code></pre>
  
  <p>This is what my current query looks like.</p>
  
  <pre><code>$stmt2 = $dbConn-&gt;prepare("SELECT COUNT(DISTINCT user_assessment.id)
  FROM user_assessment
  LEFT JOIN user ON user_assessment.user_id = user.id
  WHERE user.location = '$location'");
  $stmt2-&gt;execute();
  $stmt2-&gt;bind_result($video_count);
  $stmt2-&gt;fetch();
  $stmt2-&gt;close();
  </code></pre>
  
  <p>So my query returns all of the count for that specific location, but it doesn't omit the non-unique results from each specific user.</p>
  
  <p>Hope this makes sense, thanks for the help.</p>