COUNT( )

This MySQL function returns the number of rows retrieved in the SELECT statement for the given column. By default, rows in which the column is NULL are not counted. If the wildcard * is used as the argument, the function counts all rows, including ones with NULL values.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

COUNT([DISTINCT] expression)

Explanation

This function returns the number of rows retrieved in the SELECT statement for the given column. By default, rows in which the column is NULL are not counted. If the wildcard * is used as the argument, the function counts all rows, including ones with NULL values. If you want only a count of the number of rows in the table, you don't need GROUP BY, and you can still include a WHERE to count only rows meeting specific criteria. If you want a count of the number of rows for each value of a column, you will need to use the GROUP BY clause. As an alternative to using GROUP BY, you can add the DISTINCT keyword to get a count of unique non-NULL values found for the given column. When you use DISTINCT, you cannot include any other columns in the SELECT statement. You can, however, include multiple columns or expressions within the function.

Examples

SELECT branch_name, 
COUNT(sales_rep_id) AS number_of_reps
FROM sales_reps
JOIN branch_offices USING(branch_id)
GROUP BY branch_id;

This example joins the sales_reps and branch_offices tables together using the branch_id contained in both tables. We then using the COUNT( ) function to count the number of sales reps found for each branch (determined by the GROUP BY clause).