Never fear, this site is here

Associated: imagepaste paste nulldigital corkboard WOL client

Blog Archive Code Pictures Members Login

#MySQL fun

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.