MySQL: Introduction to Count()

Mysql offers some extremely useful, and extremely powerful tools, and among these is the count() function.
The count function asks mysql to tell you how often your request occurs in the database. This tremendously useful function can be extended across groups, joins, and even be used itself as a criteria for order by.

First, let’s start with my database. It’s a database of parents and children, related by key indexes.

Let’s find out how many entries we have:

SELECT COUNT(*)FROM children;
+----------+
| COUNT(*) |
+----------+
|   100384 |
+----------+
1 row in set (0.00 sec)
SELECT COUNT(*)FROM parents;
+----------+
| COUNT(*) |
+----------+
|    60522 |
+----------+
1 row in set (0.00 sec)

Which is interesting, but not all that useful.

First, I’d like to know how many different names we have for children:

SELECT COUNT(DISTINCT f_name) FROM children;
+------------------------+
| COUNT(DISTINCT f_name) |
+------------------------+
|                  10321 |
+------------------------+
1 row in set (0.06 sec)

Suddenly, this database is a LOT more useful. This is starting to tell us something about the data we have stored. Now you could query each name that occurred to you separately:

To see how many Bens we have, for instance:

SELECT COUNT(f_name) FROM children WHERE f_name = "Ben";
+---------------+
| COUNT(f_name) |
+---------------+
|           238 |
+---------------+
1 row in set (0.00 sec)

But that is a very cumbersome way to do something we can automate.

SELECT COUNT(f_name), f_name FROM children GROUP BY f_name ORDER BY COUNT(f_name) DESC

+---------------+--------------------------------------+
| COUNT(f_name) | f_name                               |
+---------------+--------------------------------------+
|          1110 | Steve                                |
|           977 | Simone                               |
........................................................
........................................................
+---------------+--------------------------------------+
x rows in set (0.00 sec)

You’ll get a LOT of records returned (over 10,000 in my dataset), each with the frequency of that name. I’ll break down the query.
SELECT, COUNT, and FROM you should be familiar with. Because we want to lump all data from the same first names together, we use the GROUP BY parameter to tell MySQL to do just that. The ORDER BY parameter tells mysql to sort it’s results, and DESC tells it that we want them to descend (highest values first) rather than ascend as is the default.

What if we want to look at which parents have the most children?

SELECT p.f_name, p.id, count(c.id) 
FROM children as c 
LEFT JOIN parents as p 
ON c.userid = p.id
GROUP BY c.userid
ORDER BY count(c.id) DESC;
+-----------------------------------+-------+-------------+
| f_name                            | id    | count(c.id) |
+-----------------------------------+-------+-------------+
| Monique                           |  2104 |          20 |
..........................................................
..........................................................
+-----------------------------------+-------+-------------+
x rows in set (0.01 sec)

I’ve split up the query so that it’s a bit easier to read. Apparently there is a Monique in the dataset with 20 associated child records.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s