Making sales statistics for my ERP system, but performance is bad

Making sales statistics for my ERP system, but performance is bad

I have a ERP system programmed in PHP with a mySQL database, with all my
orders for the past 4 years in it. Now I would like to make a function to
generate sales statistics. It should be possible to set search criteria
like Salesman, Department and year/period.
The sales statistics should be grouped by customer. Just like the
illustration on this link: http://maabjerg.eu/illustration_stat.png
My customers table:
customers
--------------------
id - int - auto - primary
name - varchar(100)
My orders table:
orders
-------------------
id - int - auto - primary
customerId - int
departmentId - int
salesmanId - int
orderdate - datetime
invoicedate - datetime
quantity - int
saleprice - decimal(10,2)
I had no trouble making this, but the performance is very bad. The way I
had made it before was like:
foreach($customers as $customer)
{
foreach($months as $month)
{
$sql = mysql_query("select sum(quantity*saleprice) as amount from
orders where DATE_FORMAT(invoicedate, '%m-%Y') =
'".$month."-".$_REQUEST["year"]."' AND
customerId='".$customer->id."'",$connection) or die(mysql_error());
$rs = mysql_fetch_assoc($sql);
$result[$customerId][$month] = $rs["amount"];
}
}
I hope someone can give me advice how to make this the best way.
Thanks in advance.
Steffen