Probleme requete avec SUM

Résolu/Fermé
wyllos Messages postés 160 Date d'inscription dimanche 3 juin 2007 Statut Membre Dernière intervention 15 septembre 2011 - Modifié par wyllos le 30/10/2010 à 14:39
wyllos Messages postés 160 Date d'inscription dimanche 3 juin 2007 Statut Membre Dernière intervention 15 septembre 2011 - 30 oct. 2010 à 17:42
Bonjour,

Voila je tente de faire ressortir les ventes mensuelles depuis 2007. Ayant entre 100 et 2000 lignes par mois, je fais un select sum pour ne pas que la requette dure trop longtemps. Mais ç a fonctionne tres mal et je n'arrive pas à avoir le résultat voulu.
Je vous mets les différents éléments qui pourront vous servir à m'aider:
Ma function:
 function afficher_tableau()  
    { 
 $html= ''; 
    // on fait une boucle qui lit les éléments du tableau 
 $DATEAN = array('2007','2008','2009','2010','2011','2012','2013'); 
 $DATEMOIS = array('01','02','03','04','05','06','07','08','09','10','11','12'); 
     
 global $models; 
 
  $html .= '<table><tr><th> </th><th>01</th><th>02</th><th>03</th><th>04</th><th>05</th><th>06</th><th>07</th><th>08</th><th>09</th><th>10</th><th>11</th><th>12</th></tr>'; 
  foreach($DATEAN as $dat=>$an) 
  { 
   
  $html .= '<tr><td>'.$an.'</td>'; 
   foreach($DATEMOIS as $da=>$mois) 
    { 
     
    $db = fDatabase::getInstance(); 
     $query = "SELECT SUM(total_amount), start_date FROM '".VenteModel::getTable()."' 
       WHERE  start_date LIKE '".$an."-".$mois."%'  
       GROUP BY start_date WITH ROLLUP "; 
    $db->setQuery($query); 
    $tableau = $db->loadArrayList(); 
     
           //print_r($tableau); 
     
    foreach($tableau as $table ) 
    { 
    
     //print_r($table); 
     foreach($table as $keys=>$valu) 
     { 
     $clef[$keys] = $valu; 
     //print_r($clef['start_date']); 
      
      

       $valeurs = substr($clef['start_date'],0,-15); 
       $valeurs1 = substr($clef['start_date'],5,-12); 
        
      /*$totsom = 0; 
       
        if($mois == $valeurs1) 
        { 
         
        $totsom += $clef['SUM(total_amount)']; 
         
        }*/ 
         
         
        if( empty($clef['start_date'])) 
        {  
          
         $html .='<td>'.$clef['SUM(total_amount)'].'</td>'; 
       } 
        
      } 
    
    } 
   } 

             $html .= '</tr>';  

         
  } 
  $html .= '</table>'; 
  return $html; 
    }  
}


Ma requette sort les éléments en array comme ceci:
Array ( [0] => Array ( [SUM(total_amount)] => 5734.646 [start_date] => 2009-01-01 00:00:00 )  
[1] => Array ( [SUM(total_amount)] => 5734.646 [start_date] => ) )  

Array ( [0] => Array ( [SUM(total_amount)] => 7570.636 [start_date] => 2009-02-01 00:00:00 )  
[1] => Array ( [SUM(total_amount)] => 7570.636 [start_date] => ) )  

Array ( [0] => Array ( [SUM(total_amount)] => 7659.545 [start_date] => 2009-03-01 00:00:00 )  
[1] => Array ( [SUM(total_amount)] => 0.76 [start_date] => 2009-03-08 01:00:00 )  
[2] => Array ( [SUM(total_amount)] => 0.77 [start_date] => 2009-03-12 01:00:00 )  
[3] => Array ( [SUM(total_amount)] => 0.81 [start_date] => 2009-03-19 01:00:00 )  
[4] => Array ( [SUM(total_amount)] => 7661.885 [start_date] => ) )  

Array ( [0] => Array ( [SUM(total_amount)] => 8756.3520000001 [start_date] => 2009-04-01 00:00:00 )  
[1] => Array ( [SUM(total_amount)] => 0.74 [start_date] => 2009-04-04 01:00:00 )  
[2] => Array ( [SUM(total_amount)] => 0.74 [start_date] => 2009-04-12 01:00:00 )  
[3] => Array ( [SUM(total_amount)] => 0.74 [start_date] => 2009-04-15 01:00:00 )  
[4] => Array ( [SUM(total_amount)] => 0.86 [start_date] => 2009-04-17 01:00:00 )  
[5] => Array ( [SUM(total_amount)] => 0.84 [start_date] => 2009-04-18 01:00:00 )  
[6] => Array ( [SUM(total_amount)] => 0.89 [start_date] => 2009-04-23 01:00:00 )  
[7] => Array ( [SUM(total_amount)] => 1.75 [start_date] => 2009-04-24 01:00:00 )  
[8] => Array ( [SUM(total_amount)] => 1.14 [start_date] => 2009-04-25 01:00:00 )  
[9] => Array ( [SUM(total_amount)] => 8764.0520000001 [start_date] => ) )  

Array ( [0] => Array ( [SUM(total_amount)] => 6237.908 [start_date] => 2009-05-01 00:00:00 )  
[1] => Array ( [SUM(total_amount)] => 0.87 [start_date] => 2009-05-03 01:00:00 )  
[2] => Array ( [SUM(total_amount)] => 0.74 [start_date] => 2009-05-21 01:00:00 )  
[3] => Array ( [SUM(total_amount)] => 1.82 [start_date] => 2009-05-26 01:00:00 )  
[4] => Array ( [SUM(total_amount)] => 6241.338 [start_date] => ) ) 


mais mon résultat final c'est ça:

<table><tr><th> </th><th>01</th><th>02</th><th>03</th><th>04</th><th>05</th><th>06</th><th>07</th><th>08</th><th>09</th><th>10</th><th>11</th><th>12</th></tr><tr><td>2007</td><td>23.879</td><td>23.879</td><td>605.81</td><td>605.81</td><td>106.632</td><td>106.632</td><td>380.928</td><td>380.928</td><td>1061.2655</td><td>1061.2655</td><td>642.5055</td><td>642.5055</td><td>1341.681</td><td>1341.681</td><td>2108.134</td><td>2108.134</td><td>2853.998</td><td>2853.998</td><td>145.8095</td><td>145.8095</td><td>1870.8525</td><td>1870.8525</td><td>4907.2860000002</td><td>4907.2860000002</td></tr><tr><td>2008</td><td>302.671</td><td>302.671</td><td>3804.1370000001</td><td>3804.1370000001</td><td>9154.8390000001</td><td>9154.8390000001</td><td>279.56</td><td>279.56</td><td>2517.65</td><td>2517.65</td><td>8417.1110000003</td><td>8417.1110000003</td><td>587.95599999999</td><td>587.95599999999</td><td>2395.2070000002</td><td>2395.2070000002</td><td>9952.3530000003</td><td>9952.3530000003</td><td>7032.2920000002</td><td>7032.2920000002</td><td>3397.899</td><td>3397.899</td><td>2606.858</td><td>2606.858</td></tr><tr><td>2009</td><td>5734.646</td><td>5734.646</td><td>7570.636</td><td>7570.636</td><td>7659.545</td><td>7661.885</td><td>8756.3520000001</td><td>8764.0520000001</td><td>6237.908</td><td>6241.338</td><td>3609.834</td><td>3619.744</td><td>4260.114</td><td>4261.614</td><td>2056.9130000002</td><td>5740.8530000001</td><td>2266.4050000002</td><td>2288.8150000002</td><td>6290.0061170003</td><td>6290.0061170003</td><td>4188.6578086103</td><td>4215.8378086103</td><td>4002.6694151001</td><td>4002.6694151001</td></tr><tr><td>2010</td><td>4180.1436124102</td><td>4320.8836124102</td><td>4250.9700000001</td><td>4261.8400000001</td><td>4552.4350000001</td><td>4563.8350000001</td><td>5042.1800000001</td><td>5101.2500000001</td><td>4784.5050000002</td><td>4804.5750000002</td><td>129.43</td><td>197.07</td></tr><tr><td>2011</td></tr><tr><td>2012</td></tr><tr><td>2013</td></tr></table>


sur toutes les années hors mis la 2010 toutes les sommes sont doublées. Avez-vous une idée?
Merci





window XP

1 réponse

wyllos Messages postés 160 Date d'inscription dimanche 3 juin 2007 Statut Membre Dernière intervention 15 septembre 2011 5
30 oct. 2010 à 17:42
resolu
0