Sql – Select count(*) from multiple tables to create view

countoraclesqlview

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

CREATE VIEW PLACE
AS 
SELECT  d.Loc CITY, 
        count(DISTINCT d.Deptno) N_DEPT, 
        count(e.Empno) N_EMP
FROM    Dept d 
        INNER JOIN Emp e
           ON d.Deptno = e.Deptno
GROUP BY d.Loc
Related Topic