Getting repeated value in a group_concat only if ids are different

Question

I have two tables, first the table Product:

id|category_id
--+-----------
1 | 12345
2 | 12345
3 | 12465

And then a table activity:

id|prod_id|activity_type   |description
--+-------+----------------+-----------
1 | 1     | Initialization | blah
2 | 1     | Finalization   | foo
3 | 2     | Initialization | blah again
4 | 2     | Duplication    | bar
5 | 2     | Finalization   | foobar
6 | 3     | Initialization | blob
7 | 3     | Migration      | A to B
8 | 3     | Migration      | B to C
9 | 3     | Finalization   | fuh

Now I want to retrieve for each type of activity the number of product having at least one of this kind of activity, and also the list of product category. The categories will be repeated in the list for each product of this category. For now I'm using the following query:

SELECT a.activity_type as Activity, COUNT(DISTINCT p.id) as Products,
CONVERT(GROUP_CONCAT(p.category SEPARATOR ',  ') USING utf8) AS Categories
FROM mydb.product p, mydb.activity a
WHERE p.id = a.prod_id
AND a.activity_type <> '' // To not count activities which haven't been correctly initialized
GROUP BY Categories
ORDER BY Products

Now what I await for result is:

Activity       | Products | Categories
---------------+----------+--------------------
Initialization | 3        | 12345, 12345, 12465
Finalization   | 3        | 12345, 12345, 12465
Duplication    | 1        | 12345
Migration      | 1        | 12465

But with this query I get the value '12465, 12465' for Migration. I could I get that a category appears on the list, only for each different product ids, but not for each activity of one type?

Answer 1

First group by both activity_type and prod_id and then another group by activity_type:

SELECT 
    a.activity_type AS Activity, 
    COUNT(DISTINCT p.id) AS Products,
    CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ',  ') USING utf8) 
      AS Categories
FROM 
    product AS p
  JOIN 
    ( SELECT activity_type
           , prod_id
      FROM activity 
      WHERE activity_type <> '' 
      GROUP BY activity_type
             , prod_id
    ) AS a
    ON p.id = a.prod_id
GROUP BY 
    activity_type
ORDER BY 
    Products DESC;

Tested in SQL-Fiddle (thank you @Mr.Radical)

You could also safely replace COUNT(DISTINCT p.id) with COUNT(*) in the above, as for every activity type, there are only distinct product IDs (this is taken care in the internal group by).

Answer 2

Oke, I got it solved. Try this:

SELECT a.activity_type, COUNT(DISTINCT( p.id)) AS products, 
CONVERT(GROUP_CONCAT(DISTINCT (p.category_id) SEPARATOR ',  ') USING utf8) AS Categories 
FROM activity as a 
LEFT JOIN Product AS p ON p.id = a.prod_id
WHERE a.activity_type <> ''
GROUP BY a.activity_type
ORDER BY products DESC;

Sample data:

CREATE TABLE Product
    (`id` int, `category_id` int)
;

INSERT INTO Product
    (`id`, `category_id`)
VALUES
    (1, 12345),
    (2, 12345),
    (3, 12465)
;

CREATE TABLE Activity
    (`id` int, `prod_id` int, `activity_type` varchar(14), `description` varchar(10))
;

INSERT INTO Activity
    (`id`, `prod_id`, `activity_type`, `description`)
VALUES
    (1, 1, 'Initialization', 'blah'),
    (2, 1, 'Finalization', 'foo'),
    (3, 2, 'Initialization', 'blah again'),
    (4, 2, 'Duplication', 'bar'),
    (5, 2, 'Finalization', 'foobar'),
    (6, 3, 'Initialization', 'blob'),
    (7, 3, 'Migration', 'A to B'),
    (8, 3, 'Migration', 'B to C'),
    (9, 3, 'Finalization', 'fuh')
;

http://www.sqlfiddle.com/#!2/86dac/36