Syntax
COUNT({ * | [ DISTINCT | ALL ] expr }) OVER (analytic_clause)
Purpose
This function returns the number of non-NULL values in the rows retrieved by the SELECT statement. If no matching rows are found, COUNT() returns 0. The DISTINCT option can be used to return the number of distinct values of expr.
Unlike COUNT(*), it returns the number of retrieved rows, regardless of whether they contain NULL values.
Examples
obclient> CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected
obclient> INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected
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