8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

COUNT Analytic Function

This article gives an overview of the COUNT analytic function. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Related articles.

Setup

The examples in this article require the following table.

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

COUNT as an Aggregate Function

The COUNT aggregate function returns the number of rows in a set. As an aggregate function it reduces the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated values. Using "*" or a mandatory column as a parameter returns the total number of rows in the set. Using an optional column returns the total number of rows with a non-null value in that column.

SELECT COUNT(*) AS count_total,
       COUNT(sal) AS count_sal,
       COUNT(comm) AS count_comm
FROM   emp;

COUNT_TOTAL  COUNT_SAL COUNT_COMM
----------- ---------- ----------
         14         14          4

SQL>

We can get more granularity of information by including a GROUP BY clause. In the following example we see the minimum and maximum values on a per-department basis.

SELECT deptno,
       COUNT(*) AS count_total,
       COUNT(sal) AS count_sal,
       COUNT(comm) AS count_comm
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO COUNT_TOTAL  COUNT_SAL COUNT_COMM
---------- ----------- ---------- ----------
        10           3          3          0
        20           5          5          0
        30           6          6          4

SQL>

In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.

COUNT Analytic Function

The basic description for the COUNT analytic function is shown below. The analytic clause is described in more detail here.

COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]

Omitting a partitioning clause from the OVER clause means the whole result set is treated as a single partition. In the following example we display the number of employees, as well as all the original data.

SELECT empno,
       ename,
       deptno,
       sal,
       COUNT(*) OVER () AS amount
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL     AMOUNT
---------- ---------- ---------- ---------- ----------
      7369 SMITH              20        800         14
      7499 ALLEN              30       1600         14
      7521 WARD               30       1250         14
      7566 JONES              20       2975         14
      7654 MARTIN             30       1250         14
      7698 BLAKE              30       2850         14
      7782 CLARK              10       2450         14
      7788 SCOTT              20       3000         14
      7839 KING               10       5000         14
      7844 TURNER             30       1500         14
      7876 ADAMS              20       1100         14
      7900 JAMES              30        950         14
      7902 FORD               20       3000         14
      7934 MILLER             10       1300         14

SQL>

Adding the partitioning clause allows us to return the count within a partition.

SELECT empno,
       ename,
       deptno,
       sal,
       COUNT(*) OVER (PARTITION BY deptno) AS amount_by_dept
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL AMOUNT_BY_DEPT
---------- ---------- ---------- ---------- --------------
      7782 CLARK              10       2450              3
      7839 KING               10       5000              3
      7934 MILLER             10       1300              3
      7566 JONES              20       2975              5
      7902 FORD               20       3000              5
      7876 ADAMS              20       1100              5
      7369 SMITH              20        800              5
      7788 SCOTT              20       3000              5
      7521 WARD               30       1250              6
      7844 TURNER             30       1500              6
      7499 ALLEN              30       1600              6
      7900 JAMES              30        950              6
      7698 BLAKE              30       2850              6
      7654 MARTIN             30       1250              6

SQL>

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause.

AVG * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION_DETAILS
PREDICTION_PROBABILITY PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions *
ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM *
VAR_POP * VAR_SAMP * VARIANCE * String Aggregation Top-N Queries

For more information see:

Hope this helps. Regards Tim...

Back to the Top.