Query for counting in MySQL

Question

I'm pretty sure it is a very simple question but I'm new to mysql so I would appreciate some help.. I would like to use the following table in order to count how many times each item is registered for each ID, and generate a new table from there with columns corresponding to each distinct item (a count vector for each ID). So for example, having this:

ID         ITEM
-----------------
0001        345
0001        345
0001        120
0002        567
0002        034
0002        567
0003        567
0004        533
0004        008

I want this:

    ID        008   034   120   345   533   567
  ----------------------------------------------
   0001        0     0     1     2     0     0
   0002        0     1     0     0     0     2
   0003        0     0     0     0     0     1
   0004        1     0     0     0     0     0

Thank you

IDs should be **unique**, if they are not unique, then they are not an ID! — Naftali, Jul 29 '13 at 17:47
@Neal You might have a situation where multiple rows have a reference ID to another table. For instance, in my Orders table I have multiple UserID's because each user can have multiple orders. — General_Twyckenham, Jul 29 '13 at 17:49
@General_Twyckenham that does not seem like what the OP is asking... — Naftali, Jul 29 '13 at 17:50
@Neal Question was unclear, but that's still always a possibility. — General_Twyckenham, Jul 29 '13 at 17:51
Dynamic field names in MySQL isn't very straight forward to say the least. What's the reason for wanting that exact structure? — Joachim Isaksson, Jul 29 '13 at 17:52
I think you're looking for `pivot table`, but I'm not sure how to do that in MySQL. — GolezTrol, Jul 29 '13 at 17:53
@JoachimIsaksson I want to create count vectors for each customer so I can then pass them to a python program — user2578185, Jul 29 '13 at 17:55

score 0 · Accepted Answer · answered Jul 29 '13 at 18:19

Slightly different from what you asked for, but easier on the complex SQL;

SELECT id, item, 
   (SELECT COUNT(item) FROM Table1 WHERE id=a.id AND item=a.item) cnt
FROM (SELECT DISTINCT a.id,b.item FROM Table1 a, Table1 b) a
ORDER BY id, item;

This will create a row per element instead of per customer, a'la;

ID  ITEM    CNT
1   8       0
1   34      0
1   120     1
1   345     2
1   533     0
1   567     0
2   8       0
2   34      1
...

The query above isn't very nice to MySQLs optimizer, so will probably perform slowly (an index on id/item may help). Much easier on the database is if you don't care about the zero columns;

SELECT id, item, COUNT(item) cnt
FROM Table1
GROUP BY id, item
ORDER BY id, item;

ID  ITEM    CNT
1   120     1
1   345     2
2   34      1
2   567     2
3   567     1
...

Both mean a little bit more work on the Python side, but avoid a lot of complexity for the database.

An SQLfiddle with both in action.

Query for counting in MySQL

1 Answers1