If Sum

[nealios]

Hello,

I am executing a query in which i am adding together the fields matching the expensetype then if the vatid is equal to '2' i calculate the vat on top.

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, expenses WHERE expenditure.expenseid = expenses.expenseid AND`extransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "'
GROUP BY amount";

However when i add in a second expense type in this case 'petrol' it doesnt calculate. Can anyone help?

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, expenses WHERE expenditure.expenseid = expenses.expenseid AND`extransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "'
GROUP BY amount";


Many thanks