Dear all,
we are using Oracle 10g R2 64bit on windows server 2008 64 bits.
i have the following tables
CREATE TABLE dept
( DEPARTMENT_ID NUMBER primary key,
DEPARTMENT_NAME VARCHAR2(100) NOT NULL,
DEPARTMENT_TYPE CHAR(1) NOT NULL, /* S for section, D for DEPARTMENT*/
REFERENCE_DEPARTMENT NUMBER/* Self reference to department_id */
);
alter table dept add constraint fk_dept_id_reference_dept foreign key REFERENCE_DEPARTMENT references dept(department_id);
insert into dept values (1,'ETC','D',Null);
insert into dept values (2,'CSS','S',1);
insert into dept values (3,'ESS','S',1);
commit;
create table emp (
empno number primary key,
ename varchar2(1000),
department_id number references dept(department_id)
);
insert into emp values (1,'Jones',1);
insert into emp values (2,'Smith',2);
insert into emp values (3,'Scott',3);
commit;
now we need a query which select the total employees in each department and the department name,
we use the following query
select count(e.empno) Total_Employees,d.department_name from emp e, dept d
where e.department_id=d.department_id;
The result is showing the section Names as well, while we want to display a result like below
Total_Employees Department_Name
3 ETC
we dont want to display the section names, of course we want to count the employees of all the sections of a department, be we only want to display the department Name.
hope i am clear in describing my problem.
Thank you.