[PHP-DB] Re: Complicated query on very simple database - pointers sought

[Hugh Bothwell]

"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"
."Year"
."Month"
."Customers placing orders"
."Orders placed"
."Average orders per customer"
."12-month average"
."\n";

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"
."$yr"
."$mon"
."{$row['customers']}"
."{$row['orders']}"
."$avg"
."$twelvemonth_avg"
."<\tr>";
}

echo "\n";



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

[PHP-DB] Re: Complicated query on very simple database - pointers sought

[Benjamin Walling]

Look into Aggregate Functions.  They can let you do the things that you are
looking for.

SELECT Month(OrderDate) as 'Month', Count(DISTINCT Customer) as
'NumCustomersOrdering', Count(Customer) as 'TotalOrders' FROM tbl_Orders
GROUP BY Month(OrderDate)

will give you a list of months, and the number of Customer Orders.  Throw in
a distinct if you want to know how many different customers ordered.  The
GROUP BY clause basically tells it when to reset the counter.  You can get
sums, averages, counts, etc using aggregates.

Check this page for more information:
http://www.mysql.com/doc/G/r/Group_by_functions.html

(I mainly use MSSQL, so the syntax might be slightly different - refer to
the URL above for specifics).


"George Pitcher" <[EMAIL PROTECTED]> wrote in message
03b701c20190$8f53fe00$630db092@HLATITUDE">news:03b701c20190$8f53fe00$630db092@HLATITUDE...
> Hi all,
>
> I want to report some figures to my colleagues.
>
> 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.
>
> My work with MySQL has never been this detailed.
>
> Can anyone give me any pointers?
>
> Regards
>
> George in Edinburgh
>



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