ORACLE COUNT()

오라클 COUNT()

 

 COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function

If you specity DISTINCT, then you can sepcify only the query_partition_clause of the analytic_clause.  The order_by_clause and windowing_clause are not allowed.

 

If you speify expr. then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr.

 

If you specify the asterisk(*0 then this function returns all rows, including duplicates and nulls. COUNT never returns null.

 

많이 익숙한 함수.

COUNT 는  절대 NULL 을 리턴하지 않는다.

 

Aggregate Examples

 

SQL > SELECT COUNT(*) AS TOTAL FROM EMPLOYEES ;

   --> 107

 

 

Analytic Example

The following example calculages, for each employee in the employee table, the moving count of employees earning salaries in the range 50 less than througn 150 greater than the employee's salary.

 

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

 

 

 

SQL > SELECT last_name, salary, 
                    COUNT(*) OVER(ORDER BY salary ) AS mov_count 
          FROM employees
          ORDER BY salary, last_name ;