MySQL SELECT COUNT DISTINCT

Question

I am getting from the below query

SELECT co.mobility_theme, COUNT(DISTINCT ca.user_id) AS n 
FROM courses_apply AS ca LEFT JOIN dy41s_courses AS co ON ca.course_id = co.id 
WHERE ca.submission IS NULL AND ca.`call` LIKE  '1b' 
GROUP BY co.mobility_theme 
ORDER BY co.mobility_theme ASC

The below result total (133)

mobility_theme n

1 =>70

4 =>18

5 =>45

What I want to do is: for each mobility theme (1,2 and 5), I have repeated users, so the total unique user_id should be (130), I cannot manage to get unique user_id group by (mobility theme), any help?

what am i misunderstanding about your problem, i have a repeat user, i get the expected results. sqlfiddle.com/#!2/11466/1 — Drew
– Drew, Commented May 15, 2013 at 14:01

Gordon Linoff · Accepted Answer · 2013-05-15 14:27:48Z

3

One approach is to arbitrarily choose one of the mobility themes. This method chooses the one with the minimum value:

select mobility_theme, COUNT(*) as Numusers
from (select ca.user_id, MIN(co.mobility_theme) as mobility_theme
      from courses_apply ca LEFT JOIN
           dy41s_courses co
           ON ca.course_id = co.id 
      WHERE ca.submission IS NULL AND ca.`call` LIKE  '1b' 
      group by ca.user_id
     ) caco
group by mobility_theme

answered May 15, 2013 at 14:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

MySQL SELECT COUNT DISTINCT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related