Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

mysql
count
database

How to use the COUNT function in MySQL

Aaron Xie

The COUNT function returns the number of rows in a table. With this function, you can count all the rows that fulfill a specified condition.

COUNT has three forms: COUNT(*), COUNT(expression), and COUNT(DISTINCT expression).

To visualize each function form, we will be using the data set numbers with the values below as an example.

SELECT * FROM count_num;

COUNT(*)

The COUNT(*) function returns the number of rows in a dataset using the SELECT statement. The function counts rows with NULL, duplicate, and non-NULL values.

SELECT COUNT(*) FROM numbers;

You can also use the WHERE clause to specify a condition.

SELECT COUNT(*) 
FROM numbers
WHERE val = 5;

COUNT(expression)

The COUNT(expression) function returns the number of rows that do not contain NULL values.

SELECT COUNT(val) 
FROM numbers;

Row six contains a NULL val, so the expression will return 66.

COUNT(expression)

The COUNT(DISTINCT expression) function returns the number of distinct non-NULL values.

SELECT COUNT(DISTINCT val) 
FROM numbers;

Row 11 and 22 share the same value, row 33 and 55 share the same value and row 66 has a NULL value, which leaves only 44 distinct non-NULL rows.

RELATED TAGS

mysql
count
database

CONTRIBUTOR

Aaron Xie
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring