Count(COUNT) Analytic Functions in Oracle SQL

Count is used to count the number of rows. You can use DISTINCT, query_partition_clause which used in analytic functions.

Note: order_by_clause and windowing_clause are not allowed.

Example:
1. Count is used to count no of rows present in tables.

SELECT COUNT(*) FROM employees;

COUNT(*)
----------
107


2. Count no of employees having salary greater than 5000 with where clause.

SELECT COUNT(*) FROM employees WHERE salary > 5000;

COUNT(*)
----------
58


3. Count with distinct clause, count no of manager from manger id column.

SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;

Managers
--------
18


4. Count with group by clause, Count manager has how many count of employee report.

SELECT manager_id,COUNT(*) FROM employees GROUP BY manager_id;

MANAGER_ID COUNT(*)
---------- ----------
108 5
124 8
121 8
145 6
1


5. Use Analytic function with OVER clause.
Count of employees present in each department with complete details

SELECT employee_id,
first_name,
department_id,
salary,
COUNT(*) OVER (PARTITION BY department_id) AS amount_by_dept
FROM employees;

EMPLOYEE_ID FIRST_NAME    DEPARTMENT_ID  SALARY AMOUNT_BY_DEPT
----------- ------------- ------------- ------- --------------
        200 Jennifer                 10    4400              1
        201 Michael                  20   13000              2
        202 Pat                      20    6000              2
        114 Den                      30   11000              6
        115 Alexander                30    3100              6
        116 Shelli                   30    2900              6
        117 Sigal                    30    2800              6
        118 Guy                      30    2600              6
        119 Karen                    30    2500              6

6. Use ANalytic function with OVER clause.
Count of employees earning salaries in the range 50 less than through 150 greater than the employee’s salary.

SELECT last_name, salary,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND
150 FOLLOWING) AS mov_count
FROM employees
ORDER BY salary, last_name;

LAST_NAME           SALARY  MOV_COUNT
--------------- ---------- ----------
Olson                 2100          3
Markle                2200          2
Philtanker            2200          2
Gee                   2400          8
Landry                2400          8
Colmenares            2500         10
Marlow                2500         10
Patel                 2500         10
Perkins               2500         10