Confusing query - my brain hurts!

[hessodreamy]

The query I'm trying to do involves totalling sales by month or week for a product or products.

The compicated thing is the results I am looking for will feature an undefined number of products and an undefined number of months/weeks. I can't figure out how to do it in 1 query, and have been doing 1 query to get the relavent products, and a query for each product to get the sums of the sales for date ranges, like so:

SELECT prodCode, productID from tProducts WHERE prodCode LIKE 'hy1%'

and for each product:

select from_unixtime(uts,'%m %Y') as date, sum(prodQty) as quantity from tOrders o INNER JOIN tOrderPOs pos ON o.orderID = pos.orderID INNER JOIN tOrderProds p ON pos.purchaseOrder = p.purchaseOrder WHERE productID = 45 AND uts > 1125702000 AND uts <= 1128380400 group by from_unixtime(uts,'%m %Y')

So i'm putting the sales totals for each product into an array.

The down side of this approach is that a given product matching the search criteria may not have an results for a particular month. So 1 product might return 6 rows, while another returns 2 rows. Meaning that at some point I have to manipulate the results to display in a coherent fashion.

What I really want is a query that will handle results like this:

productID | january | february | march | april | may
HY1 | 5 | 3 | 7 | 0 | 0

or this:

productID | january | february | march
HY1 | 5 | 3 | 7
HY67 | 0 | 0 | 5

if you see what I mean. Is this possible to have a query with a flexible number of fields? Or can someone suggest how best to approach this?
I'm dead confused!!!

[Sxooter]

What I'd do is a left join to get the dates etc...

I.e. create a temp table with the dates you want to work with, then do something like:

select month from datetable left join myfirsttable on (datetable.month=myfristtable.month) inner join mynexttable...

That way you'll just get NULLs for the months that don't have numbers in them, and the table will be the right width every time.

[coreyp_1]

I don't know of any way to make variable columns directly, but you can use a loop to build your query.

I had to guess at your database structure. I also wasn't able to test the code, so you will probably have to modify it some to make it fit your use.

Here's my stab at it:

// php psuedocode:
// $startmonth, $startyear, $endmonth, and $endyear are variables you provide
// I assume "uts" means Unix Time Stamp, so I wrote the code using time stamps

$query = "SELECT productID FROM tProducts";

$curmonth = mktime(0, 0, 0, $startmonth, 1, $startyear);// The month you want to start in (timestamp), ex. 6/1/2005
$endmonth = mktime(0, 0, 0, $endmonth, 1, $endyear);// The month you want to end in (timestamp) ex. 8/1/2005
$nextmonth = mktime(0, 0, 0, $startmonth+1, 1, $startyear);
while ($curmonth <= $endmonth) { // this will include the ending month
  list($xyear, $xmonth) = explode('-', date('Y-F', $curmonth));
  $query .= "LEFT JOIN (
  SELECT SUM(prodQty) as $xmonth-$xyear
    FROM tOrders
    INNER JOIN tOrderPOs USING orderID
    INNER JOIN tOrderProds USING purchaseOrder
    WHERE ( uts BETWEEN $curmonth AND ".$nextmonth-1." )
    GROUP BY productID
  ) USING productID
";
  $curmonth = $nextmonth;
  $nextmonth = mktime(0, 0, 0, $curmonth+1, 1, $curyear)
}

$query .= "WHERE prodCode LIKE 'hy1%'";

Hopefully this will put out a query like:

productID | january-2005 | february-2005 | march-2005
HY1       | 5            | 3             | 7 
HY67      | 0            | 0             | 5

[hessodreamy]

I think thats the right approach, but I couldn't get your code to work - probably because I'm on mysql 4.0 which doesnt support subqueries (should have mentioned that) but it did lead me to a similar approach of having a conditional statement to calculate a total for each column.

So there query generated would be as such:

SELECT prodCode, prodName, 
SUM(IF(from_unixtime(uts,'%M') = 'January' ,prodQty,0)) as January, 
SUM(IF(from_unixtime(uts,'%M') = 'February' ,prodQty,0)) as February, 
SUM(IF(from_unixtime(uts,'%M') = 'March' ,prodQty,0)) as March, 
SUM(IF(from_unixtime(uts,'%M') = 'April' ,prodQty,0)) as April, 
SUM(IF(from_unixtime(uts,'%M') = 'May' ,prodQty,0)) as May, 
SUM(IF(from_unixtime(uts,'%M') = 'June' ,prodQty,0)) as June, 
SUM(IF(from_unixtime(uts,'%M') = 'July' ,prodQty,0)) as July, 
SUM(IF(from_unixtime(uts,'%M') = 'August' ,prodQty,0)) as August, 
SUM(IF(from_unixtime(uts,'%M') = 'September' ,prodQty,0)) as September, 
SUM(IF(from_unixtime(uts,'%M') = 'October' ,prodQty,0)) as October, 
SUM(prodQty) as tot 
from tOrders o 
INNER JOIN tOrderPOs pos ON o.orderID = pos.orderID 
INNER JOIN tOrderProds op ON pos.purchaseOrder = op.purchaseOrder 
INNER JOIN tProducts p ON op.productID = p.productID 
WHERE 
from_unixtime(uts,'%Y') = 2005 AND 
(
from_unixtime(uts,'%M') = 'January' OR from_unixtime(uts,'%M') = 'February' OR 
from_unixtime(uts,'%M') = 'March' OR from_unixtime(uts,'%M') = 'April' OR from_unixtime(uts,'%M') = 'May' OR from_unixtime(uts,'%M') = 'June' OR 
from_unixtime(uts,'%M') = 'July' OR from_unixtime(uts,'%M') = 'August' OR 
from_unixtime(uts,'%M') = 'September' OR from_unixtime(uts,'%M') = 'October' ) 
AND prodCode LIKE 'hy%' GROUP BY prodCode 

And the php is shown below:

$prodcondition = "";
          if($prodCompCode != "") $prodcondition = " AND prodCode LIKE '$prodCompCode%'";

        if($prodCompType == "week") $dategrp = "%u";
      else $dategrp = "%M";
      $dategrp = "from_unixtime(uts,'$dategrp')";
      $monthArray = array('January', 'February', 'March','April', 'May','June','July','August','September','October','November','December');

if($prodCompDate1 != "" OR $prodCompDate2 != "")
                    {
                      if($prodCompDate1 != "") $dateCondition[$monthArray[$prodCompDate1 -1]] = "from_unixtime(uts,'%M') = '". $monthArray[$prodCompDate1 -1]."' ";
                      if($prodCompDate2 != "") $dateCondition[$monthArray[$prodCompDate2 -1]] = "from_unixtime(uts,'%M') = '". $monthArray[$prodCompDate2 -1]."' ";
              }
              else
                    {
                      $currentMonth = date('m');
                      foreach($monthArray as $index => $thismonth) if($index< $currentMonth) $dateCondition[$thismonth] = "from_unixtime(uts,'%M') = '$thismonth' ";
                    }

$sql = "SELECT prodCode, prodName";
            foreach($dateCondition as $colName => $nextDateCondition) $sql.= ", SUM(IF($nextDateCondition,prodQty,0)) as $colName";

        $sql .= ", SUM(prodQty) as tot ";

 $sql .= " from tOrders o INNER JOIN tOrderPOs pos ON o.orderID = pos.orderID 
INNER JOIN tOrderProds op ON pos.purchaseOrder = op.purchaseOrder INNER JOIN tProducts p ON op.productID = p.productID WHERE from_unixtime(uts,'%Y') = 2005 AND (";

        foreach($dateCondition as $colName => $nextDateCondition) $sql.= "$nextDateCondition OR ";
        $sql = substr($sql,0,-4). ") ";
        $sql .= $prodcondition;

        $sql .= " GROUP BY prodCode";

It works a treat and is a million times faster than my previous approach of multiple queries.

Thanks a lot peeps!