JOIN with AND and aggregate function : COUNT « Aggregate Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Aggregate Functions
3. COUNT

SQL>
SQL>
SQL>
SQL> CREATE TABLE department
  2  (id   INT PRIMARY KEY
  3  ,name VARCHAR(30)
  4  );

Table created.

SQL>
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>
SQL>