"George Pitcher" <[EMAIL PROTECTED]> wrote in message
03b701c20190$8f53fe00$630db092@HLATITUDE">news:03b701c20190$8f53fe00$630db092@HLATITUDE...
> 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
SELECT
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;
echo
"\n<table><thead><tr>"
."<th>Year</th>"
."<th>Month</th>"
."<th>Customers placing orders</th>"
."<th>Orders placed</th>"
."<th>Average orders per customer</th>"
."<th>12-month average</th>"
."</tr>\n</thead><tbody>";
while($row = mysql_fetch_array($res)) {
// check whether to print year value
if ($row['year'] == $prevyear)
$yr = "";
else
$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;
$sum_num++;
if ($sum_num > 12) {
$sum -= $items[$index-12];
unset($items[$index-12]);
$sum_num--;
}
++$index;
$twelvemonth_avg = $sum / $sum_num;
// print table row
echo
"\n\t<tr>"
."<td>$yr</td>"
."<td>$mon</td>"
."<td>{$row['customers']}</td>"
."<td>{$row['orders']}</td>"
."<td>$avg</td>"
."<td>$twelvemonth_avg</td>"
."<\tr>";
}
echo "\n</tbody></table>";
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
