COUNT

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 between COUNT(*) and COUNT(expr) is that COUNT(*) returns the number of retrieved rows, regardless of whether they contain NULL values.

Examples

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.081 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

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.001 sec)