SQL to Break down WP-Shortstats data by year and month

UPDATE: Fixed error

If you are running WP-Shortstats it really doesn’t tell you what your monthly breakdown of unique visitors is or the yearly breakdown. In writing this I probably should have written the WP-Shortstats function to show this data, but I will do that sometime this weekend. For now here is the SQL that will break down your unique visitors by month and year. Enjoy

SELECT
	COUNT(DISTINCT remote_ip) as rip,
	MONTH(FROM_UNIXTIME(dt)) as umonth,
	YEAR(FROM_UNIXTIME(dt)) as uyear
FROM
	wp_ss_stats
GROUP BY
	uyear,umonth;

+------+--------+-------+
|  rip | umonth | uyear |
+------+--------+-------+
|  419 |      7 |  2006 |
|  365 |      8 |  2006 |
|  286 |      9 |  2006 |
|  623 |     10 |  2006 |
|  821 |     11 |  2006 |
|  773 |     12 |  2006 |
|  748 |      1 |  2007 |
| 2022 |      2 |  2007 |
| 4819 |      3 |  2007 |
| 1320 |      4 |  2007 |
+------+--------+-------+
10 rows in set (0.72 sec)

You can leave a response, or trackback from your own site.