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
