i worked at a particularly PC bank once ... Kaizen was the management
philosophy and means 'never ending horizon' or words to that effect ...
maybe their get Kaizen points per department ...
sounds lovely,
:-)
Steve
-Original Message-
From: Bob Hall [mailto:[EMAIL PROTECTED]]
Sent: 28 April 2001 23:53
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] 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
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]
--
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]
