Select statements - A Quest !!!

I have two tables namely, 1. cpkaizen : The following is the desc Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | kno | int(10) | | PRI | NULL | auto_increment | | loginid | varchar(20) | YES | | NULL | | | period | varchar(6) | YES | | NULL | | | peryear | int(5) | YES | | NULL | | | star | char(1) | YES | | N | | | name1 | varchar(30) | YES | | NULL | | | name2 | varchar(30) | YES | | NULL | | | name3 | varchar(30) | YES | | NULL | | | name4 | varchar(30) | YES | | NULL | | | date | date | YES | | NULL | | | problem | varchar(255) | YES | | NULL | | | action | varchar(255) | YES | | NULL | | | result | varchar(255) | YES | | NULL | | | benefit | varchar(255) | YES | | NULL | | | evalution_para | varchar(100) | YES | | NULL | | | dept | varchar(20) | YES | | NULL | | | name0 | varchar(30) | YES | | NULL | | (* the records in this table are KAIZENS. dept is of that employee i.e either \'ms\' or fin,com,log,epcm,po,ms,hr . *) 2. employee : following is the desc Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | edpno | int(10) | YES | | NULL | | | name | varchar(40) | | | | | | loginid | varchar(20) | YES | | NULL | | | superuser | char(1) | YES | | N | | | groupno | int(4) | YES | | NULL | | | dept | varchar(30) | YES | | NULL | | | sub_dept | varchar(30) | YES | | NULL | | (* dept is common for employees i.e \'cp\' but sub_depts are fin,com,log,epcm,po,ms,hr . So there are more than one employee in each sub_dept *) Now actually, I want to generate the report : the output should be : Department | Total no of employee | Total KAizen | Average (* here under departments should come the above seven mentioned. then total no of employees in eeach sub_dept. Then total no of KAIZENS for that sub_dept from table cpkaizen and Average which is Total no of kaizen divide by total no of employees *) How can I go ahead to get that output table generated. Thank you, Pranot