How can I select count(*) from two different tables to create view?
There are the tables:
DEPT table
- DEPTNO
- DNAME
- LOC
EMP table
- EMPNO
- ENAME
- JOB
- MGR
- HIREDATE
- SAL
- COMM
- DEPTNO
I've tried:
CREATE VIEW PLACE
AS SELECT d.Loc CITY, count(d.Deptno) N_DEPT, count(e.Empno) N_EMP
FROM Dept d, Emp e
where d.Deptno = e.Deptno
GROUP BY d.Loc, d.deptno;
and got this:
CITY N_DEPT N_EMP
CHICAGO 6 6
DALLAS 5 5
NEW YORK 3 3
There is only 1 N_DEPT in each city so the result is wrong :/.
There should be:
CITY N_DEPT N_EMP
CHICAGO 1 6
DALLAS 1 5
NEW YORK 1 3
Best Answer
use
DISTINCT
, try