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






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>








12.3.COUNT
12.3.1.COUNT(x) gets the number of rows returned by a query.
12.3.2.Passes ROWID to COUNT() and gets the number of rows
12.3.3.COUNT() with null
12.3.4.Using an aggregate with the GROUP BY clause to count by city
12.3.5.COUNT(1) from a table
12.3.6.Count column with table alias
12.3.7.Count distinct column value
12.3.8.Compare the difference between count(*) and count(distinct course)
12.3.9.Count employees in the same department
12.3.10.JOIN with AND and aggregate function
12.3.11.count(*) - count(onhand)
12.3.12.count(*) vs count(column name)
12.3.13.Count all employees by even/odd employee id
12.3.14.Count date field value, and calculation