flickr.com/photos/andrewmwhalen
linkedin.com/in/andrewmwhalen
twitter.com/awhalen
youtube.com/user/whalenaFinding Frequency Counts With SQL
January 11th, 2008 by Andrew
Finding the frequency of items in a database table is something I find myself needing to do a lot. Though for some reason when I need to do this, I always forget how, so here it is. Let’s say you have a table of tag data with two columns and a bunch of rows. Let’s say the table is a simple adjacency list of tags for posts on a blog. Here’s an example of what it could look like:
| id of tagged post | tag |
|---|---|
| 1 | sql |
| 2 | blogs |
| 3 | robots |
| 3 | llamas |
| 4 | sql |
| 5 | trees |
| 5 | blogs |
| 6 | llamas |
| 3 | blogs |
Now let’s say you want to find out how many times each tag is used. This can be done with one easy SQL statement:
SELECT tag, COUNT(*) AS total FROM tags GROUP BY tag
This will give you output like this:
| tag | total |
|---|---|
| robots | 1 |
| trees | 1 |
| llamas | 2 |
| sql | 2 |
| blogs | 3 |
This is a very useful statement for tag clouds, general statistics, and lots of other things.
05/09/2009 at 10:16 am
Fantastic – just what I was looking for. Thanks!
05/21/2009 at 12:05 am
I experimented and searched for a few hours before finding this post. I can’t believe the solution is so simple. Thanks for posting this. Suggestion: explain how to add another column with the relative frequency (i.e., count / total blob posts).
09/11/2009 at 12:43 pm
Perfect. Thank you.
01/07/2010 at 2:57 pm
Yes! Thank you! This is exactly what I was looking for, too. Thanks for sharing.
05/29/2010 at 5:28 am
Thanks a bunch man, i have been trying and trying, the solution is simple most of the times huh ?
04/21/2011 at 3:27 pm
Thank you!! This works great!
05/21/2011 at 6:03 pm
Thank you very much
09/23/2011 at 4:53 am
Awesome!!!
01/19/2012 at 6:05 pm
Ditto!
As usual, the solution appears simple, once I find it.
Perfect to grab most popular search terms from our cookies-data table.