Page 1 of 1

Confusing query - my brain hurts!

Posted: Mon Oct 03, 2005 10:38 am
by 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:

Code: Select all

SELECT prodCode, productID from tProducts WHERE prodCode LIKE 'hy1%'
and for each product:

Code: Select all

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!!!

Posted: Mon Oct 03, 2005 2:43 pm
by Weirdan
Hmm... looks like a crosstab, right? Check this manual page

Posted: Mon Oct 03, 2005 2:50 pm
by Weirdan
Please next time format your query.
And post table definitions, it's somewhat challenging to figure out where `uts` and `prodQty` fields come from.

Posted: Wed Oct 05, 2005 7:12 am
by hessodreamy
Yes, you're absolutely right on both counts. I should have posted column definitions, and a crosstab seemed to be the way to go.
I couldnt have done it without dynamically generated sql though.

So there query generated would be as such:

Code: Select all

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:

Code: Select all

$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!