Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two tables, first the table Product:

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 as Products,
CONVERT(GROUP_CONCAT(p.category SEPARATOR ',  ') USING utf8) AS Categories
FROM mydb.product p, mydb.activity a
WHERE = 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?

share|improve this question
do you mean that you want to get the number of products with a specific activity_type. For example, Migration has 1 distinct prod_id (3). You now want to only get one category_id (12465) and not 2 (12465, 12465) like you now get? – Mr. Radical Jan 21 '13 at 16:10
up vote 3 down vote accepted

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

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

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

You could also safely replace COUNT(DISTINCT 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).

share|improve this answer
thank you for the credit. – Mr. Radical Jan 21 '13 at 16:28
Thanks a lot, I didn't knew one could use a Select query as some kind of pseudo-table. Should I consider that the result of such a query is in fact a table? – Eldros Jan 23 '13 at 9:38
It depends on what you define as "table". These are usually called "derived tables". Meaning that they are not base tables, they are not permanently stored. There is also another type of table, called views. They are similar to the derived tables but they have a name and they can be used in many other queries (without the need to define them every time as the derived table here). Views are also not base tables and are usually not stored permanently (except some DBMS that allow them to be "persisted"). So, to answer your question, yes, such a query is a table but not a "base" table. – ypercubeᵀᴹ Jan 23 '13 at 9:55
And that many people when they use the term "table" they really or often mean "base table". – ypercubeᵀᴹ Jan 23 '13 at 9:55

Oke, I got it solved. Try this:

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

Sample data:

    (`id` int, `category_id` int)

    (`id`, `category_id`)
    (1, 12345),
    (2, 12345),
    (3, 12465)

    (`id` int, `prod_id` int, `activity_type` varchar(14), `description` varchar(10))

    (`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')

share|improve this answer
My first thought was this, too. But it doesn't show 3 items in the Categories columns (as wanted) in the "Initialization" and "Finalization" groups. – ypercubeᵀᴹ Jan 21 '13 at 16:32
@ypercube you are correct my query doesn't show the requested 3 items. Yours answer does. – Mr. Radical Jan 21 '13 at 17:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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