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|
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:
This is a very useful statement for tag clouds, general statistics, and lots of other things.