Do this query:
SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID
Then use this code:
$revenues = array();
foreach( $results as $result )
{
$revenues[ $result['Year'] ][ $result['Month'] ][ $result['CustomerID'] ] = $result['Revenue'];
}
The result will be a simple multi-dimensional array with a minimal SQL query and minimal application code.
- Gabriel
On Thursday, October 16, 2003, at 01:19 PM, Rory McKinley wrote:
Hi Jeff
OK, aliasing table is creating a copy of one table but calling it something
different, so you compare a table to itself e.g.:
FROM revenue a, revenue b, revenue c COULD ALSO BE FROM revenue AS a, revenue AS b, revenue AS c
I am referencing revenue three times but have aliased it as a, b, and c to
make sure that my predicate makes sense.
As for the loop, I can give you something off the top of my head in rough
(very!) PHP , if you don't come right, I can sit down and do the code a
little more detailed
For simplification purposes, I am going to assume that you can alias tables
as numbers (e.g. 1, 2, 3 instead of a, b, c) but I suggest you check if this
is possible - if you can't there is a work around that just requires a bit
more thought....
//Assume you have an array that has all your client ids in
$client_id_array.
//Create base values based on the first id...
$select_base = "YEAR(1.date) AS year, MONTH(1.date) AS month,SUM(1.revenue) AS cust1_rev"
$for_base = "FROM revenue 1"
$predicate_base = "WHERE 1.customer_id = ".$client_id_array[0]
//Now loop through and append additional items to each string for each instance of a client
//Start at 1 not zero as we already have accounted for the first id above
for($j=1; $j < count($client_id_array); $j++)
{
$select_base = $select_base.', SUM('.($j+1).') AS cust'.($j+1).'_rev';
$for_base = $for_base.', revenue '.($j+1);
$predicate_base = $predicate_base.' AND (YEAR('.($j+1).'.date) = YEAR(1.date) AND MONTH('.($j+1).'.date) =MONTH(1.date) AND '.($j+1).'.customer_id = '.$client_id_array[$j].')';
}
//Once your loop is done, put the parts together
$query = $select_base.$for_base.$predicate_base;
If you can't use numbers, you can use single letters, but that requires a
little more work incrementing ASCII numbers and then converting to letters -
also makes things way more complicated if you have more than 26 clients :)
but still doable.
HTH
Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] "There are 10 kinds of people in this world, those who understand binary and those who don't" (Unknown) ----- Original Message ----- From: "Jeff McKeon" <[EMAIL PROTECTED]> To: "Rory McKinley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, October 16, 2003 6:51 PM Subject: RE: Challenging query....
If you have a way to generate the query code dynamically (e.g. using a loop in C, PHP etc.), you can build a query using aliased tables :
SELECT YEAR(a.date) AS year, MONTH(a.date) AS month, SUM(a.revenue) AS cust1_rev, SUM(b.revenue) AS cust2_rev, SUM(c.revenue) AS cust3_rev FROM revenue a, revenue b, revenue c WHERE a.customer_id = 1 AND (YEAR(b.date) = YEAR(a.date) AND MONTH(b.date) = MONTH(a.date) AND b.customer_id = 2) AND (YEAR(c.date) = YEAR(c.date) AND MONTH(c.date) = MONTH(c.date) AND c.customer_id = 3) GROUP BY year, month
Ok, that looks promising as I'll be using PHP, but I'm a little fuzzy on
the logic you've set. What are "aliased tables" and how would I define,
use them in an loop?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
