Name
COUNT()
Synopsis
COUNT([DISTINCT] expression
)
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 those 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. Here is
an example:
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 use the
COUNT()
function to count the number of sales
reps found for each branch (determined by the GROUP
BY
clause).
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.