Declaration
COUNT({ * | [ DISTINCT | ALL ] expr }) OVER (analytic_clause)
Description
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.
The difference with COUNT(*) is that it returns the number of rows retrieved, 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