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 censuses 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. The COUNT() function counts the number of non-NULL results, 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 together all records for each owner. Without it, all you get is an error message:

mysql> SELECT owner, COUNT(owner) FROM pet;
ERROR 1140 at line 1: 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 characterising 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 sex 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 known-sex animals:

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

I would reccomend the SQL tutorial at

http://www.sqlcourse.com

but use your new mySQL database in place of their
online database, as theirs often doesn't work!

I would very much like to see some examples
with "NULL" Variables used in sort columns.. I
noticed some interesting things trying to get my
Date column to return NULL variables last, but
keep an ascending order..

I did ORDER BY due DESC and ORDER BY due, the
results were predictable, NULL came last in the
first one, and first in the second one.. But i
wanted NULL to come last, but the rest of the
data to have an ascending order.

I tried SELECT to_days(now())-to_days(due) as
test FROM jobs ORDER BY test and ORDER BY test
DESC.. The interesting thing, was that the NULL
always showed up first in this list...

Needless to say, im still trying to work around
this..

It would be GREAT to see an example of count using
2 tables. For example one table contains artists and
another table contains a list of works and use count
to get a list of all artists and how many works they
have done (including artists who have no works ie.
count = 0 )

Use this example if you want to make a "word
sensitive" search in any field containing text:

SELECT count(*) FROM table WHERE dateused >='yyyy-
mm-dd hh:mm:ss' AND dateused <='yyyy-mm-dd
hh:mm:ss'
AND field_name REGEXP "[^a-zA-Z]brbr[^a-zA-Z]
|^brbr[^a-zA-Z]"

I've been having problems with counting rows from
to different tables in one query.
"SELECT count(tabel1.column), count
(tabel2.column) FROM tabel1,tabel2"
takes post1*post2 for some reason.

I have NO idea if this is the proper way to do this
(READ: This has not been heavily tested so don't
flame me!), but I figured out a way to do what
Glenn mentioned a few comments back.

Let's say you have a table with a list of artists
like so:


+-----------+-------------+
| artist_id | artist_name |
+-----------+-------------+
| 1 | Rembrandt |
| 2 | Raphael |
| 3 | Picasso |
| 4 | O'Keeffe |
+-----------+-------------+


And a list of paintings that are associated with
these artists like so:


+-------------+--------------------------------+-----------+
| painting_id | painting_name |
artist_id |
+-------------+--------------------------------+-----------+
| 1 | The raising of Lazarus |
1 |
| 2 | Bathsheba at Her Bath |
1 |
| 3 | The return of the prodigal son |
1 |
| 4 | The nymph Galatea |
2 |
| 5 | The Burning of the Borgo |
2 |
| 6 | Don Quixote |
3 |
+-------------+--------------------------------+-----------+

In order to view the artists and their number of
works *including* those that have no works, I
needed to do the following query:
select
artist.artist_name,count(painting.painting_id)
from artist left join painting on
artist.artist_id=painting.artist_id group by
artist.artist_id;

In order to get the result:


+-------------+-----------------------------+
| artist_name | count(painting.painting_id) |
+-------------+-----------------------------+
| Rembrandt | 3 |
| Raphael | 2 |
| Picasso | 1 |
| O'Keeffe | 0 |
+-------------+-----------------------------+


I hope that posted properly and I hope this helps.
If
it doesn't work for you, I'd be interested to hear.

Counting rows from different tables in one query:

SELECT COUNT(DISTINCT table1.ID) AS table1, COUNT(DISTINCT table2.ID) AS table2, COUNT(DISTINCT table3.ID) AS table3 FROM table1, table2, table3

DISTINCT in COUNT() is supported in version 3.23.2 and upper.

I do manage a large white page system with millions
of records that is in hosting at <a
href='http://www.wide.it'>wide hosting</a> and
when I do counting I would like to set a row limit: is
that possible to do like:

SELECT COUNT(*) FROM WHITEPAGE WHERE NAME
LIKE 'JOHN%' LIMIT 1000 ?

To avoid problems with a field named Count(*) if
you are using this to create tables or anything else,
use a query similar to:
CREATE TABLE proxy.webhits SELECT Domain,
User_Name, Site, Count(*) AS Hits FROM rawlog
GROUP BY User_Name, Site
Take special note of the AS. Because once
a table
with a Count(*) column has been created, you
cannot rename the column because it contains a
reserved word. Which can cause other problems.


This method should be included in these instructions,
because, as a novice, I just spent half an hour
digging through webpages that all assume people
already know that or something.

Adam Newman correctly points out that it's a good
idea to use AS to rename your column when using
CREATE TABLE ... SELECT ... However, if you have
named your column something like COUNT
(DISTINCT uid), you can rename it like this:

ALTER TABLE newtable
CHANGE `COUNT(DISTINCT id)` num_uids INT;

what I want:

SELECT tblEmployee.First_Name AS First, tblEmployee.Last_Name AS Last,
QCA_NCSC_Results.EmployeeID as ncsceid, QCA_NRA_Results.EmployeeID as nraeid,
COUNT(DISTINCT QCA_NCSC_Results.EmployeeID) AS NCSC,
COUNT(DISTINCT QCA_NRA_Results.EmployeeID) AS NRA
FROM tblEmployee, QCA_NCSC_Results, QCA_NRA_Results
WHERE tblEmployee.EmployeeID=QCA_NCSC_Results.EmployeeID
AND tblEmployee.EmployeeID=QCA_NRA_Results.EmployeeID
GROUP BY tblEmployee.EmployeeID LIMIT 0, 30

however this returns NO results, if I remove
AND tblEmployee.EmployeeID=QCA_NRA_Results.EmployeeID

from the clause I get results, however the count for NRA is completly bogus.

What would be the best what to achieve a count of 2 table records that match another table?

I have a query that works in php using a while statement, however it is too CPU intensive...

Add your own comment.