php mysql GROUP BY and sum

[mdannatt]

Hi there, can anyone help me with this? I have this mysql query in php. THe code below currently shows this:

- - 25000.00 - 0.60 - 1.00 - 1.00 - 15000.00000000

- - 10000.00 - 0.60 - 1.00 - 1.00 - 6000.00000000

- - 23000.00 - 0.50 - 1.25 - 1.00 - 14375.00000000

- - 18000.00 - 0.50 - 1.25 - 1.00 - 11250.00000000

- - 16500.00 - 0.70 - 1.00 - 1.20 - 13860.00000000

- - 14250.00 - 0.70 - 1.00 - 1.00 - 9975.00000000

- - 25000.00 - 0.60 - 1.00 - 1.00 - 15000.00000000

- - 42250.00 - 1.35 - 1.00 - 1.20 - 68445.00000000

 

The reson this works is because I have set the GROUP BY to Supplier_ID. What I am trying to achieve is GROUP and SUM by say, contributionlevel. If I use GROUP BY contributionlevel instead of Suuplier_ID, I now get

- - 42250.00 - 1.35 - 1.00 - 1.20 - 68445.00000000

- - 30750.00 - 0.70 - 1.00 - 1.20 - 13860.00000000

- - 60000.00 - 0.60 - 1.00 - 1.00 - 15000.00000000

- - 41000.00 - 0.50 - 1.25 - 1.00 - 14375.00000000

The left hand column is group correctly but the right hand column is not. I have used

(supplierinfo.procurementspend * contributionlevel.cpoints * vasupplier.vpoints * edrecipient.epoints ) AS adjustedprocurement in SELECT. Does anyone have any idea how to ensure the right hand column numbers (adjustedprocurement) GROUP and SUM correctly to match the left most column? My script is:

 

$calc =
"SELECT supplierinfo.Supplier_ID , supplierinfo.contributionlevel , sum(supplierinfo.procurementspend) , contributionlevel.cpoints , vasupplier.vpoints , edrecipient.epoints ,
(supplierinfo.procurementspend * contributionlevel.cpoints * vasupplier.vpoints * edrecipient.epoints ) AS adjustedprocurement,
sum(supplierinfo.procurementspend * contributionlevel.cpoints * vasupplier.vpoints * edrecipient.epoints ) AS sumadprocurement
FROM edrecipient
LEFT JOIN supplierinfo ON edrecipient.edrecipient_ID = supplierinfo.edrecipient_ID
LEFT JOIN contributionlevel ON supplierinfo.Level_ID = contributionlevel.Level_ID
LEFT JOIN vasupplier ON supplierinfo.vasupplier_ID = vasupplier.vasupplier_ID
WHERE procurementdate >='$startdate'
	AND certficateexpiry >='$startdate'	
	AND procurementdate <='$enddate'
	AND certficateexpiry <='$enddate'
GROUP BY supplierinfo.contributionlevel
LIMIT 0 , 30"; 
$calcres = mysql_query($calc)or die("Error: " . mysql_error());
//Lets print out the multiplier tables
echo"<br />";
while($calcresult = mysql_fetch_assoc($calcres)){
echo $calcresult['(supplierinfo.Supplier_ID)']." - ". $calcresult['supplierinfo.contributionlevel']. " - ". $calcresult['sum(supplierinfo.procurementspend)']." - ". $calcresult['cpoints']
." - ". $calcresult['vpoints']." - ". $calcresult['epoints']." - ".$calcresult['adjustedprocurement'];
echo "<br />";
}

 

Thanks for the help!

 

 

[fenway]

If I think I get what you're saying, it's that the non-aggregated columns are producing garbage... correct/

[kickstart]

Hi

 

Do you want to group by both Supplier_ID and contributionlevel? SQL is going to struggle if you have non aggregate columns specified that are not in the group by clause (ie, if there are 2 different values which one would it return with the aggregated values).

 

All the best

 

Keith