There is a table org (name, emp_id). For each possible name value, you must output the number of unique emp_id . Can this be done with the help of count() or how else?

UPD: A little decipher. Let's say

 (name) (emp_id) apple 85962 apple 14654 intel 14654 intel 14654

As a result, you need to get something like

 (name) count(emp_id) apple 2 intel 1

those. if emp_id repeated in different emp_id , then they must be counted

    4 answers 4

    First interpretation of your question. For each name, calculate a unique number of emp_id

     SELECT name, COUNT(DISTINCT emp_id) AS qty FROM org GROUP BY name

    The second interpretation is to calculate so that the emp_id occurring with different emp_id would be excluded emp_id To do this, let's go from the reverse, calculate the number of the name the emp_id , that is, we need to exclude the emp_id the several name from the sample emp_id

     SELECT name, COUNT(DISTINCT emp_id) AS qty FROM org WHERE NOT emp_id IN ( SELECT emp_id FROM org GROUP BY emp_id HAVING COUNT(DISTINCT name) > 1 ) GROUP BY name

    To unify or not your business, depending on what is lying there and how

    • Hmm ... Checked in the work. After they voted for this answer, I checked it again, but the result is not correct . I have nothing against it, but something is wrong here. - Deonis
    • Why is it not correct: Petya 5, 100 Vasya 15, 20 Total two each)) - YN
    • For the second interpretation, it is because 30, 10, and 1 are found in both Vasya and Petit, if you look at your example) —YN
    • @ YYYY your first interpretation is practically what I need. But there are difficulties that I added in the question - co11ter
    • And what problems? Request the first interpretation and gives the desired result - SCN
     SELECT `emp_id` FROM `table_name` GROUP BY `emp_id` HAVING COUNT(`emp_id`) = 1

    UPD

    Somehow everything is very tricky for everyone. )) If you need to compare not only the value of the emp_id field, but also the corresponding name field, then why not combine them?

     SELECT `emp_id`, CONCAT(`name`,`emp_id`) AS uv FROM `uniq` GROUP BY uv HAVING COUNT(uv) = 1

    Here is what we get in the end: http://sqlfiddle.com/#!2/afac1/1

    All that remains to be done is to calculate the number of unique entries and group them by the name field. Final request:

     SELECT t1.`name`,COUNT(t1.`emp_id`) AS cnt FROM `uniq` t1 WHERE CONCAT(t1.`name`,t1.`emp_id`) IN ( SELECT CONCAT(t2.`name`,t2.`emp_id`) AS uv FROM `uniq` t2 GROUP BY uv HAVING COUNT(uv) = 1 ) GROUP BY t1.`name`

    Those wishing to see in action - http://sqlfiddle.com/#!2/afac1/14/0 That's all, as it were))

    • And if different names have the same emp_id? - skegg
    • @mikillskegg, the challenge is to find unique values in the emp_id field? Or did I miss something? - Deonis
    • > For each possible name value, you must output the number of unique emp_id. - skegg

    group by name maybe? And then their distinct

    update 1

    ok, see here Mysql: Select unique values ​​within the group?

    Well, in any case, there will be subqueries.

    update 2

     select t1, count(t2) as t4 from ( select substring(new1,0,charindex('/',new1)) as t1, substring(new1,charindex('/',new1)+1,len(new1)) as t2, new1 from (select distinct name + '/' + emp_id as new1 from org) temp1 ) temp2 group by t1
    • distinct simply removes repetitions and "pulls off" repeating into one. Those. count returns the total number of value types - skegg
    • here without a subquery cannot be, because you first need to "uniquely" something like from (select distinct name + '/' + emp_id as tmp from org) and from this to do group by and count - vlukham
    • Share the code if it is not difficult. - skegg
    • shared, there is no more time, if you roll, then thumbs up :) - vlukham

    Yoksel!

     select name, count(distinct emp_id) from my_temp group by name ;