"George Pitcher" <[EMAIL PROTECTED]> wrote in message
> My database (2 fields: Customer, Order date) contains over 15000 records
> dating back to 1999.
> I want to be able to show, in a web page, the following information
> Month           Customers placing orders             Orders placed
> Average Orders/Cust      Average over prev 12 months.

... if you know 'orders placed' and 'number of customers',
'average orders per customer' is easily calculated; and
'average over prev 12 months' doesn't fit well into this query,
would be better done in PHP.

How about

  MONTH(orderdate) AS month,
  YEAR(orderdate) AS year,
  COUNT(DISTINCT customer) AS customers
  COUNT(customer) AS orders
FROM mytable
GROUP BY year,month
ORDER BY year,month ASC

then in PHP,

// month-to-name
$months = array("", "Jan", "Feb", "Mar", "Apr",
    "May", "Jun", "Jul", "Aug", "Sept", "Oct",
    "Nov", "Dec");

// for running 12-month average
$sum = 0;
$sum_num = 0;
$item = array();
$index = 0;

// for printing start-of-year-only years
$prevyear = 0;

        ."<th>Customers placing orders</th>"
        ."<th>Orders placed</th>"
        ."<th>Average orders per customer</th>"
        ."<th>12-month average</th>"

while($row = mysql_fetch_array($res)) {
    // check whether to print year value
    if ($row['year'] == $prevyear)
        $yr = "";
        $yr = $prevyear = $row['year'];

    // get month-name
    $mon = $months[$row['month']];

    // calculate this-month average orders per customer
    $avg = $row['orders'] / $row['customers'];

    // update 12-month average
    $items[$index] = $avg;
    $sum += $avg;
    if ($sum_num > 12) {
        $sum -= $items[$index-12];
    $twelvemonth_avg = $sum / $sum_num;

    // print table row

echo "\n</tbody></table>";

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to