Take the 2-minute tour ×
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.

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?

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

2 Answers 2

up vote 3 down vote accepted

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).

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
1  
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( 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

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

 
discard

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.