>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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
