MySQL SELECT Multiple DISTINCT COUNT

Question

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 DISTINCT values for each user.

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 COUNT. 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 DISTINCT's in the query, but am not sure how to do this.

+-----+----------+----------+
| 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 |
+-----+----------+----------+

This is what my current query looks like.

$stmt2 = $dbConn->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->execute();
$stmt2->bind_result($video_count);
$stmt2->fetch();
$stmt2->close();

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.

Hope this makes sense, thanks for the help.

No solution but an optimization: use INNER JOIN. It is faster than LEFT JOIN, and the effect is the same, since you use the joined table in the WHERE clause. — GolezTrol
– GolezTrol, Commented Oct 16, 2012 at 15:47
@GolezTrol, yes thank you. It's just how I copied the code that I lost the line breaks. I've edited my post to make it a little easier to read. — Jako
– Jako, Commented Oct 16, 2012 at 15:48
@Jako . . . Your description and your sample query/data have nothing to do with each other. You use words like "location" and "video", and yet these are not in the query. Can you revise the question to make it clearer what you want? — Gordon Linoff
– Gordon Linoff, Commented Oct 16, 2012 at 15:49
@GordonLinoff Sorry about that, tweaked the question a bit to help. — Jako
– Jako, Commented Oct 16, 2012 at 15:51

Newbo.O · Accepted Answer · 2012-10-16 15:58:19Z

6

SELECT COUNT(DISTINCT ua.video_id, ua.user_id)
FROM user_assessment ua
INNER JOIN user ON ua.user_id = user.id
WHERE user.location = '$location'

You can write a lot of things inside a COUNT so don't hesitate to put what you exactly want in it. This will give the number of different couple (video_id, user_id), which is what you wanted if I understood correctly.

answered Oct 16, 2012 at 15:58

Newbo.O

1,9481 gold badge13 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GolezTrol Over a year ago

+1 I wasn't sure and didn't have a database at hand to test, but if this works, it is better than my answer. More compact, more clear and probably faster.

GolezTrol · Accepted Answer · 2012-10-16 15:51:31Z

1

The query below joins a sub-query that fetches the distinct videos per user. Then, the main query does a sum on those numbers to get the total of videos for the location.

SELECT 
  SUM(video_count)
FROM 
  user u
  INNER JOIN 
  ( SELECT
      ua.user_id,
      COUNT(DISTINCT video_id) as video_count
    FROM
      user_assessment ua
    GROUP BY
      ua.user_id) uav on uav.user_id = u.user_id
WHERE 
  u.location = '$location'

Note, that since you already use bindings, you can also pass $location in a bind parameter. I leave this to you, since it's not part of the question. ;-)

answered Oct 16, 2012 at 15:51

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT Multiple DISTINCT COUNT

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related