Count is another commonly used aggregate operation that finds the total number of values. MySQL COUNT is the aggregate operation that finds the count of all the not null values in one or more data columns or expression in a MySQL data table. The count function can be used for columns of any MySQL data type and it returns a BigInt output.
Syntax of MySQL COUNT Function
The count function is used in a SELECT query with given syntax.
COUNT([DISTINCT] *| expression|table_column)
- COUNT is the keyword for MySQL count operation.
- DISTINCT is the keyword. It is optional. When this keyword is used in the query only distinct values from column or expression are used to count the values and returned by SELECT statement.
- table_column is the column name of the table for which the values are to be counted.
- Expression is the expression created with table column names and MySQL Arithmetic Operators or functions. Only those expression values will be counted which are not null values after evaluation of expression.
Examples of MySQL COUNT
All the examples below will use artists table with this data.
Counting all rows
In this example the COUNT function is used with *. * represents all the columns of the table. This query returns the count of all rows in the table.
SELECT COUNT(*) FROM artists;
COUNT with an expression
The following query returns the count of not null values after the evaluation of expression. The expression consists of DATEDIFF function to find difference between current date (CURDATE() function) and dob columns in artists table. The complete data of artists table shows that 4 out of 10 rows of this table have null values for dob column. So DATEDIFF function in query generated NULL for these 4 rows and thus the COUNT function gives 6 as output eliminating the NULL values from count operation.
SELECT COUNT(DATEDIFF(CURDATE(),dob)) FROM artists;
Count rows for two different columns
The following query shows counting of not null values in artist_name and dob columns.
SELECT COUNT(artist_name), COUNT(dob) FROM artists;
COUNT distinct values
This query returns the count of distinct country names by using DISTINCT keyword in MySQL COUNT function.
SELECT COUNT(DISTINCT country) FROM artists;
COUNT with where clause
Where clause can be added to the SELECT statement with COUNT to filter data for counting. The COUNT function will count only those values which match the condition in WHERE clause.
This query will return count of rows with band_id=2
SELECT COUNT(*) FROM artists WHERE band_id=2;
This query will return count of rows with country name as ‘USA’
SELECT COUNT(*) FROM artists WHERE country='USA';
Points to remember
- Count function can be used multiple times in one SELECT statement. Preferably it is used with different column of the table or different expressions. COUNT(*) will include all the columns of the specified table.
- COUNT function can be used only in a SELECT statement.
- DISTINCT keyword is used within parenthesis before column name or expression to include count of distinct values.
- COUNT returns zero if no values are there or all values are NULL.