How To: MySQL Select AVG of COUNT

This has gotten a lot of people stuck, and so I wanted to share a code snippet that lets you select the average of a column that you are using COUNT on in a MySQL Select query. We used this to find the average daily visitors to websites in our web analytics app Cloud Control. By taking advantage of some of MySQLs built in date functions, we can fairly easily extract the averages of the data. Note you could extend the second select to include this such as if statements and other queries to get exactly the data you want.
SELECT   day_of_week, 
         AVG(visits) as avg_visits, 
         AVG(views) as avg_views 
         FROM
             (
                  SELECT
                  DAYNAME(visit_date) as day_of_week,
                  DAYOFWEEK(visit_date) as day_num,
                  TO_DAYS(visit_date) as date,
                  COUNT(id) as visits, 
                  SUM(pageviews) as views                   
                  FROM visit_log 
                  GROUP BY date
              ) temp
GROUP BY day_of_week
ORDER BY day_num
/* In PHP this will return an array with the keys 0 thru 6
0 being Sunday, and fits perfectly into PHP's native date function. 
Example: */
Array => 
(
      [0] => Array 
           (
             [day_of_week] => Sunday
             [avg_visits] => 202.75
             [avg_views] => 320.12
            )
      [1] => Array 
           (
             [day_of_week] => Monday
             [avg_visits] => 105.25
             [avg_views] => 121.21
            )
      [2] => Array 
           (
             [day_of_week] => Tuesday
             [avg_visits] => 125.42
             [avg_views] => 204.11
            )
      [3] => Array 
           (
             [day_of_week] => Wednesday
             [avg_visits] => 115.23
             [avg_views] => 198.90
            )
      [4] => Array 
           (
             [day_of_weel] => Thursday
             [avg_visits] => 154.12
             [avg_views] => 301.53
            )
      [5] => Array 
           (
             [day_of_week] => Friday
             [avg_visits] => 205.24
             [avg_views] => 413.65
            )
     [6] => Array 
           (
             [day_of_week] => Saturday
             [avg_visits] => 198.93
             [avg_views] => 521.10
            )
)

/* If you wanted to show the averages for the current day, simply do this
assuming that the query I showed you above is $query */
$result_array = mysql_query($query);
echo "Today's average visit count is ";
echo $result_array[date("w")]['avg_visits'];

/* To show yesterdays average pageviews */
$result_array = mysql_query($query);
$timestamp = strtotime("yesterday");
echo "Yesterdays avg pageviews ";
echo $result_array[date("w", $timestamp)]['avg_views'];
Small dog web development studio

View All Our Posts

Web application developers in western Washington, creating user centric web solutions for businesses of all shapes and sizes.

Our projects include ecommerce, event organization, user driven web applications and more.


Small Dog Studios, LLC. Small Dog Studios News