Calculate Visitor Statistics In SQL

2011-02-07

This article is a sequel to Visitor statistics with PHP. Here are some SQL queries that use the database table that was created in the previous article. I do not go through exactly how the questions are constructed but provides you with a few examples. It is up to you how you use them if you know what you are dealing with. The comments in the code describes each query’s function.

-- Get visitors per day during the past 30 days.
SELECT day, COUNT(*) AS count FROM stats GROUP BY day ORDER BY day DESC LIMIT 0,30;

-- Get visitors per hour
SELECT hour, COUNT(*) AS count FROM stats GROUP BY hour ORDER BY hour ASC;

-- Get visitors per browser.
SELECT browser, COUNT(*) AS count FROM stats GROUP BY browser ORDER BY count DESC;

-- Get total number of unique visitors.
SELECT COUNT(DISTINCT IP) AS count FROM stats;

-- Get visitors per day and hour.
SELECT day, hour, COUNT(*) AS count FROM stats GROUP BY hour, day ORDER BY day DESC, hour DESC;

Then it’s up to you with how to use the queries. The code in the example below, can give results as shown in Figure 1.

//Total views
$total = mysql_num_rows(mysql_query("SELECT * FROM stats"));

//Browsers
$browsers = mysql_query("SELECT browser, COUNT(*) AS count FROM stats GROUP BY browser ORDER BY count DESC;");
echo("<table border=\"1\">");
while($row = mysql_fetch_array($browsers))
{
	$percent = round(($row['count']/$total)*100);
	echo("<tr><td>".$row['browser']."</td><td>".$row['count']."</td><td>".$percent."%</td></tr>");
}
echo("</table>");

Figure 1: Screenshot of statistics script