Additional languages
« 3.3.4.7 Pattern Matching
3.3.4.9 Using More Than one Table »
Section Navigation      [Toggle]

3.3.4.8. Counting Rows

Databases are often used to answer the question, “How often does a certain type of data occur in a table?” For example, you might want to know how many pets you have, or how many pets each owner has, or you might want to perform various kinds of census operations on your animals.

Counting the total number of animals you have is the same question as “How many rows are in the pet table?” because there is one record per pet. COUNT(*) counts the number of rows, so the query to count your animals looks like this: 

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

Earlier, you retrieved the names of the people who owned pets. You can use COUNT() if you want to find out how many pets each owner has: 

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

Note the use of GROUP BY to group all records for each owner. Without it, all you get is an error message: 

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) 
with no GROUP columns is illegal if there is no GROUP BY clause

COUNT() and GROUP BY are useful for characterizing your data in various ways. The following examples show different ways to perform animal census operations.

Number of animals per species: 

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

Number of animals per sex: 

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

(In this output, NULL indicates that the sex is unknown.)

Number of animals per combination of species and sex: 

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

You need not retrieve an entire table when you use COUNT(). For example, the previous query, when performed just on dogs and cats, looks like this: 

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = 'dog' OR species = 'cat'
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

Or, if you wanted the number of animals per sex only for animals whose sex is known: 

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

User Comments

Posted by Michael Dibbets on April 28 2005 6:42pm[Delete] [Edit]

For example, this is a table of yours:

|---ID---|---Amount--|
|X:183812|---$3.00---|
|00391123|---$4.00---|
|X:203985|---$0.00---|
And you wish to select all ID's with an X: in front of it, but you have like 10000 of them, but you need to know how many you have, without having to use the php function mysql_numrows after a mysql_query, because that would mean that all the data is loaded into memory, held ready by the mysql server... that's a bit of a memory hog.
Then use this code(only for php, modify to your own liking)

$test1 = mysql_query("SELECT COUNT(*) FROM [tablename] WHERE `ID` LIKE 'X:%'");
$test2 = mysql_fetch_row($test1);
echo "$test2[0]";

Just my 2 cents ;-)

Michael

Posted by Patrick Mineault on August 10 2005 7:22pm[Delete] [Edit]

If you are using SELECT DISTINCT on a complex join clause, you might be stumped to find the count without returning the whole recordset. For example:

SELECT DISTINCT COUNT(*) AS theCount FROM table1, table2 WHERE table1.user_id = table2.user_id

Will not return the same number for 'theCount' as you would have using mysql_num_rows and:

SELECT DISTINCT table1.user_id FROM table1, table2 WHERE table1.user_id = table2.user_id

This is because DISTINCT affects the user_id, not the count. If you try something like:

SELECT DISTINCT table1.user_id, COUNT(*) AS theCount FROM table1, table2 WHERE table1.user_id = table2.user_id

Then you'll get an error saying there's no GROUP BY clause. The key is to collapse all of the rows using GROUP BY NULL:

SELECT DISTINCT table1.user_id, COUNT(*) AS theCount FROM table1, table2 WHERE table1.user_id = table2.user_id GROUP BY NULL

Posted by Sherzod Ruzmetov on October 4 2005 5:41am[Delete] [Edit]

Regarding the DISTINCT with COUNT() on joins, try the following instead. It works for me:

SELECT COUNT(DISTINCT table_name.rowname) FROM table_name WHERE ....

Posted by negnin on January 10 2007 4:58pm[Delete] [Edit]

If you use distinct and joins and just want a count, you can also put your complete query between parentheses and use:
SELECT COUNT(*) FROM (your_query) AS Foo;

Add your own comment.