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
Average what?
(* 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
Sir, from looking at your tables, I can't tell which contains the parent records and which contains the child records. I might be able to figure it out if know what a kaizen is. You haven't declared any primary keys on the employee table, and you didn't say which DBMS you're using.
In general, pattern is
SELECT dept, Count(p.something), Count(c.something_else), Avg(some_column)
FROM parent_table AS p INNER JOIN child_table AS c ON p.id = c.p_id
GROUP BY dept;
The actual syntax depends on your DBMS.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database