MySQL in a Nutshell, 2nd Edition

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).