Syntax
COUNT({ * | [ DISTINCT | ALL ] expr }) OVER (analytic_clause)
Purpose
COUNT() returns 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
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