MySQL fun

This is an old post I wrote, which was originally posted on neverfear.org on Mon, 25th Aug 2008 22:55:32. Archived here for posterity.

SELECT count(DISTINCT postkey) as post_count, membername FROM members, posts where members.memberid = posts.postauthorid GROUP BY membername ORDER BY post_count DESC

Posting that query is mostly for archival purposes. Basically this.. fun little query counts the number of unique posts by each user, from a MySQL database.

The posts table has postkey (the unique identifier) and postauthorid, the members table has a membersid column (relates to postauthorid) and membersname (the human-readable member's name).

It outputs something like..

post_count, membername
13, ben
10, doug
2, AtnNn
1, Neil

A simplified version (without the relational table stuff) would be

SELECT count(DISTINCT postkey) as post_count, postauthorid GROUP BY postauthorid

Basically count(DISTINCT postkey) returns the unique post count ( as post_count stores the count as post_count ). , postauthorid grabs the author ID, so we can tell who posted how many. GROUP BY postauthorid is requried by MySQL for reasons beyond my knowledge of SQL (if you omit it, MySQL returns an error demanding a GROUP statement)

And here ends a haphazard, sleep-deprived MySQL tutorial.