Please help! Sum problem

[nealios]

Hello,

 

I am attempting to add together the total amount of the transactions where the field matches the expensetype.

 

Then any field where the vatid is equal to '2'  i want to calculate the vat on top.

 

Then i want to add the two figures together.

 

The following code works when i do it for one expense type (Tools).

 

 

SELECT amount,SUM(IF(expensetype ='Tools', ROUND(amount,2), 0.00)) AS toolstotal,
SUM(IF(expensetype ='Tools' AND vatid='2',ROUND(amount * 0.175, 2), 0.00)) AS toolsvat,
SUM(IF(expensetype ='Tools' AND vatid='2',ROUND(amount * 1.175 ,2), amount)) AS toolsnet

FROM expenditure WHERE`extransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "' GROUP BY amount";

 

However when i add in a second expense type in this case 'petrol' it doesnt calculate. I don't know why its not working?

 

 

 

 

SELECT amount,SUM(IF(expensetype ='Tools', ROUND(amount,2), 0.00)) AS toolstotal,
SUM(IF(expensetype ='Tools' AND vatid='2',ROUND(amount * 0.175, 2), 0.00)) AS toolsvat,
SUM(IF(expensetype ='Tools' AND vatid='2',ROUND(amount * 1.175 ,2), amount)) AS toolsnet,

SUM(IF(expensetype ='Petrol', ROUND(amount,2), 0.00)) AS petroltotal,
SUM(IF(expensetype ='Petrol' AND vatid='2',ROUND(amount * 0.175, 2), 0.00)) AS petrolvat,
SUM(IF(expensetype ='Petrol' AND vatid='2',ROUND(amount * 1.175 ,2), amount)) AS petrolnet

FROM expenditure WHERE`extransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "' GROUP BY amount";

 

Many thanks

[Illusion]

Your first query is working fine? ???

[nealios]

Yeah it works, but as soon as i add another set of expense types its ruins the result of the first set and neither calculate properly.

 

Im stumped!