MySQL count on Grouping

Question

I want to count the number of times a grouping occurs in a query.

SELECT COL1, 
       COL2, 
       *(count COL1)* 
  FROM TABLE 
 GROUP BY COL1, COL2

So I'm expecting to see the results like so:

COL1, COL2, Count

A,    A,    1
A,    B,    2
B,    A,    1
B,    B,    2
B,    C,    3

Count(*) will give me the sum of the grouped row which is what I don't want, but I just cant seem to get my head round it or find a way to do it. Anyway, thanks in advance.

Answer 1

You should add auto_increment column to make an order. After that, try something like this:

 SELECT a.col1, a.col2,
                  (SELECT Count(*) From T1 c 
                   WHERE c.col1 = a.col1 
                   AND c.ID <= a.ID) as count
 FROM T1 a;

SQL Fiddle

OR Without auto_increment Try this:

SELECT col1, col2,
   (select count(*) from T1 t2
    where t2.col2 <= t1.col2 and
          t2.col1 = t1.col1 
   ) as Enumeration
FROM T1 t1
GROUP BY col1, col2;

SQL Fiddle

Answer 2

Your query (with the parentheses appropriately placed) does what you want:

SELECT COL1, COL2, count(*)
FROM TABLE
GROUP BY COL1, COL2;

If you want the sum of a column, then use the sum() function.

EDIT:

If you are trying to enumerate the groups (despite what the question is asking in rather clear English), you can do:

SELECT col1, col2,
       (select count(*)
        from table t2
        where t2.col1 = t.col1 and
              t2.col2 <= t.col2
       ) as Enumeration
FROM table t1
GROUP BY col1, col2;