CODEWITHSUNDEEP
mysqlcountgroup-bydistincthaving
ashish
ashish

Reputation: 99

getting the count of distinct duplicate ids in mysql

this is the query

select count(*), 
       ss.pname, 
       ttu.user_id, 
       ttl.location_name , 
       group_concat(em.customer_id), 
       count(em.customer_id)
  from seseal as ss, 
       track_and_trace_user as ttu, 
       track_and_trace_location as ttl, 
       eseal_mapping as em
 where ss.real_id=em.e_id 
   and em.user_id=ttu.user_id 
   and ttu.location_id=ttl.location_id
group by ss.pname, ttu.user_id, ttl.location_name 
having count(em.customer_id)>1 ;

and following is the results:

+----------+----------------+---------+---------------+------------------------------+-----------------------+
| count(*) | pname          | user_id | location_name | group_concat(em.customer_id) | count(em.customer_id) |
+----------+----------------+---------+---------------+------------------------------+-----------------------+
|        6 | Nokia N91      |       1 | Malad         | 60,51,60,51,58,58            |                     6 |
|        2 | SUPERIA 1000gm |       4 | Raichur       | 51,46                        |                     2 |
|        5 | SUPERIA 1000gm |       5 | west bengal   | 51,46,51,51,46               |                     5 |
|        2 | SUPERIA 500gm  |       4 | Raichur       | 59,59                        |                     2 |
|        3 | SUPERIA 500gm  |       5 | west bengal   | 59,46,59                     |                     3 |
+----------+----------------+---------+---------------+------------------------------+-----------------------+

Now the problem is, as you can see in result set, the second last column in some rows the customer_ids are duplicate and in some rows are unique. And the last column is giving the count of it.
Now what i want is to pick the 3rd row, there are two customer ids namely 51 and 46 and these are duplicate in that row, so my last column for this row should contain 2.
Similarly for last row my last column should contain 1 as there is only one customer id which is duplicated i.e. 59.
So if you understand the exact problem then the 2nd row should not be part of this result set as it doesn't contain any customer ids that are duplicate.

Upvotes: 4

Views: 1159

Answers (1)

Alain Collins
Alain Collins

Reputation: 16362

How about:

group_concat(distinct em.customer_id)

and 

count(distinct em.customer_id)

Upvotes: 1

Related Questions