mysql - Counting instances in the table -

i have table millions of records. records this:

    name    group +--------+------+ |  aaa   |   1  | |  bbb   |   2  | |  ccc   |   1  | |  aaa   |   1  | |  aaa   |   2  | +--------+------+ 

• each name can repeated multiple times in 1 group.
• each name can in multiple groups.
• there lot of groups

i need display "report" following information:

1. how many times each name occure in table (sorted highest lowest).
2. how many times these names occure in each group.

of course, won't display information names, want display first 100 names (with occurences).

example of desired output:

   name   count   group1  group2 +------+-------+-------+-------+ | aaa  |   3   |   2   |   1   | | bbb  |   1   |   0   |   1   | | ccc  |   1   |   1   |   0   | +------+-------+-------+-------+ 

so far, counted names using query:

select *      (select name, count(name) count names s       group name order count desc) r  limit 100 

i cannot figure out how count names returned above query in each group.

in mysql, can use shorthand follows:

drop table if exists my_table;  create table my_table (id int not null auto_increment primary key ,name    varchar(12) not null ,category int not null );  insert my_table (name,category) values ('aaa',1), ('bbb',2), ('ccc',1), ('aaa',1), ('aaa',2);  select * my_table; +----+------+----------+ | id | name | category | +----+------+----------+ |  1 | aaa  |        1 | |  2 | bbb  |        2 | |  3 | ccc  |        1 | |  4 | aaa  |        1 | |  5 | aaa  |        2 | +----+------+----------+   select name      , count(*) total      , sum(category = 1) cat1      , sum(category = 2) cat2    my_table   group      name; +------+-------+------+------+ | name | total | cat1 | cat2 | +------+-------+------+------+ | aaa  |     3 |    2 |    1 | | bbb  |     1 |    0 |    1 | | ccc  |     1 |    1 |    0 | +------+-------+------+------+