Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Aggregate Functions

 

JOIN with AND and aggregate function

SQL> SQL> CREATE TABLE department   2  (id   INT PRIMARY KEY   3  ,name VARCHAR(30)   4  ); Table created. SQL> SQL> CREATE TABLE employee   2  (id   INT PRIMARY KEY   3  ,name VARCHAR(30)   4  ,rank VARCHAR(30)   5  ,dept INT REFERENCES department(id)   6  ); Table created. SQL> INSERT INTO department VALUES (1,'Ladies'' Outfitting'); 1 row created. SQL> INSERT INTO department VALUES (2,'Gent'' Outfitting'); 1 row created. SQL> SQL> INSERT INTO employee VALUES (1,'Mr. Abc','Assistant',1); 1 row created. SQL> INSERT INTO employee VALUES (2,'Mrs. Bcd','Supervisor',1); 1 row created. SQL> INSERT INTO employee VALUES (3,'Miss. Cee','Assistant',2); 1 row created. SQL> INSERT INTO employee VALUES (4,'Mr. Jack','Assistant',2); 1 row created. SQL> SQL> SELECT department.name, COUNT(employee.id)   2    FROM department JOIN employee ON department.id=dept   3   GROUP BY department.name; NAME                           COUNT(EMPLOYEE.ID) ------------------------------ ------------------ Ladies' Outfitting                              2 Gent' Outfitting                                2 SQL> SQL> DROP TABLE employee CASCADE CONSTRAINT; Table dropped. SQL> DROP TABLE department CASCADE CONSTRAINT; Table dropped. SQL>