COUNT

Syntax

COUNT(expr)

Description

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value. It is an aggregate function, and so can be used with the GROUP BY clause.

COUNT(*) counts the total number of rows in a table.

COUNT() returns 0 if there were no matching rows.

COUNT() can be used as a window function.

Examples

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);   INSERT INTO student VALUES   ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),   ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),   ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),   ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);  SELECT COUNT(*) FROM student; +----------+ | COUNT(*) | +----------+ | 8 | +----------+

COUNT(DISTINCT) example:

SELECT COUNT(DISTINCT (name)) FROM student; +------------------------+ | COUNT(DISTINCT (name)) | +------------------------+ | 4 | +------------------------+

As a window function

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);  INSERT INTO student_test VALUES   ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),   ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),   ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),   ('Tatiana', 'SQL', 87);  SELECT name, test, score, COUNT(score) OVER (PARTITION BY name)   AS tests_written FROM student_test; +---------+--------+-------+---------------+ | name | test | score | tests_written | +---------+--------+-------+---------------+ | Chun | SQL | 75 | 2 | | Chun | Tuning | 73 | 2 | | Esben | SQL | 43 | 2 | | Esben | Tuning | 31 | 2 | | Kaolin | SQL | 56 | 2 | | Kaolin | Tuning | 88 | 2 | | Tatiana | SQL | 87 | 1 | +---------+--------+-------+---------------+

See Also

• SELECT

• COUNT DISTINCT

• Window Functions

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}