MySQL – trying to combine `GROUP_CONCAT` with `COUNT()`

countgroup-concatenationMySQL

I am trying to combine GROUP_CONCAT with COUNT(), and failing.

I have the following data:

CREATE TABLE `tab1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `team` varchar(45) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `item` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'phone');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'john', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'john', 'phone');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team2', 'mark', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team2', 'phil', 'phone');

This gives me:

1   | team1 | bob   | car
2   | team1 | bob   | car
3   | team1 | bob   | phone
4   | team1 | john  | car
5   | team1 | john  | phone
6   | team2 | mark  | car
7   | team2 | phil  | phone

I am trying to get to:

team1   | bob   | car(2), phone(1)
team1   | john  | car(1), phone(1)
team2   | mark  | car(1)
team2   | phil  | phone(1)

The closest I have managed is:

SELECT 
A.team,
A.name,
GROUP_CONCAT(DISTINCT CONCAT(A.item, "(", B.count, ")") SEPARATOR ' , ') AS groupings
FROM tab1 A
JOIN (
    SELECT team, name, item, count(item) AS count
    FROM tab1 
    GROUP BY team, name, item
    ) B
GROUP BY team, name

But this gives me

team1   | bob   | car(1) , phone(1) , car(2) , phone(2)
team1   | john  | phone(1) , car(1) , car(2) , phone(2)
team2   | mark  | car(1) , car(2)
team2   | phil  | phone(1) , phone(2)

I've tried every possible combination of GROUP BY I can think of, but it still doesn't work. What have I missed please?

Best Answer

You've already found your answer, but here are the reasons:

The reason you're getting duplicates is because you're joining tab1 with itself without criteria on the join, which is simply resulting in duplicates. The query can be pretty easily accomplished without the join, looking like this:

SELECT A.team, A.name, GROUP_CONCAT(A.count SEPARATOR ' , ')
FROM
   (SELECT team, name, CONCAT(item, '(', count(item), ')') AS count     
    FROM tab1
    GROUP BY team, name, item) A
GROUP BY A.team, A.name;
Related Question