1

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?

2

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;
  • that's even better! cheers for that. – IGGt Aug 7 '15 at 15:09
0

I knew it would end up being something simple, and it was:

WHERE A.team=B.team AND A.name=B.name AND A.item=B.item

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.