Mysql – How to query when one ID has more than one name and find the total count of unique names, id

mysql-5.6

Same id having more than one name and the count of the unique names which are associated with the unique id

enter image description here

Best Answer

SELECT id, COUNT(DISTINCT id, name) `count`
FROM `table`
GROUP BY id WITH ROLLUP

If you do not need in total count, it's simplified to:

SELECT id, COUNT(DISTINCT name) `count`
FROM `table`
GROUP BY id