Dave Donaldson

Below is the SQL for getting a statistical summary of your blog. Statistics included in this SQL are: post count, referral count, referral average, web view count, web view average, aggregator view count, aggregator view average, comment count, and comment average. Just replace the value of the UserName to match your blog's virtual directory.

Some caveats to running this SQL. This was written for version 0.95.2004.102 of .Text and hasn't been tested with any other versions. And in order to actually run the SQL, you must have access to the database with all the .Text stored procedures.

DECLARE @referralCount int
DECLARE @referralAvg decimal(6, 2)
DECLARE @webCount int
DECLARE @webAvg decimal(7, 2)
DECLARE @aggCount int
DECLARE @aggAvg decimal(7, 2)
DECLARE @commentCount int
DECLARE @commentAvg decimal(5, 2)
DECLARE @postCount decimal(5, 0)
DECLARE @blogID int

/* Get blog ID, post count, and comment count */
SELECT
    @blogID = BlogID,
    @postCount = PostCount,
    @commentCount = CommentCount
FROM blog_Config
WHERE UserName = 'dave'   /* Replace value here */

/* Get referral count */
SELECT @referralCount = SUM([Count])
FROM blog_Referrals
WHERE BlogID = @blogID

/* Get total # of web views and aggregator views */
SELECT
    @webCount = SUM(WebCount),
    @aggCount = SUM(AggCount)
FROM blog_EntryViewCount
WHERE BlogID = @blogID

/* Calculate the averages */
SET @referralAvg = @referralCount / @postCount
SET @webAvg = @webCount / @postCount
SET @aggAvg = @aggCount / @postCount
SET @commentAvg = @commentCount / @postCount

/* Set the output */
SELECT
    @postCount AS 'PostCount',
    @referralCount AS 'ReferralCount',
    @referralAvg AS 'ReferralAverage',
    @webCount AS 'WebCount',
    @webAvg AS 'WebAverage',
    @aggCount AS 'AggCount',
    @aggAvg AS 'AggAverage',
    @commentCount AS 'CommentCount',
    @commentAvg AS 'CommentAvg'