Syntax
COUNT({ * [ DISTINCT ALL ] expr }) OVER (analytic_clause)
Purpose
You can call this function to return the number of non-NULL values in rows retrieved by using the SELECT statement. If no matching row is found, COUNT() returns 0. You can use the DISTINCT option to return the number of distinct values of expr.
This function differs from COUNT(*) in that COUNT(*) returns the number of retrieved rows, regardless of whether the rows contain NULL values.
Examples
obclient> CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.08 sec)
obclient> INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.11 sec)
obclient> INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.01 sec)
obclient> SELECT LAST_NAME, COUNT(*) OVER (PARTITION BY JOB_ID) COUNT_S FROM EXPLOYEES;
+-----------+---------+
LAST_NAME COUNT_S
+-----------+---------+
JIM 1
MIKE 3
LILY 3
TOM 3
+-----------+---------+
4 rows in set (0.00 sec)