Finding 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.

Comments

16 Responses to “Finding Frequency Counts With SQL”

  1. Fantastic – just what I was looking for. Thanks!

  2. 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).

  3. Perfect. Thank you.

  4. Yes! Thank you! This is exactly what I was looking for, too. Thanks for sharing.

  5. Thanks a bunch man, i have been trying and trying, the solution is simple most of the times huh ? 🙂

  6. Thank you!! This works great!

  7. Thank you very much 😉

  8. Awesome!!!

  9. Ditto!
    As usual, the solution appears simple, once I find it.
    😉
    Perfect to grab most popular search terms from our cookies-data table.

  10. Cheers ! 😀

  11. Thanks! Even though this thread was started in 2008, it is still helping me today! Good work!

  12. Shot a lot bru ! Was sitting on this for about an hour.

  13. Thanks, this is exactly what I was looking for. Was thinking about it for a couple of days…

  14. Hi! Thank you for this.
    What if I want to add the total number of records to the same table?

  15. Thanks bro

  16. Thank you sir. This is what makes the internet great. A kind of shared collective memory, and your contrition is wonderful.

Leave a Reply