Help with IF statement

[nealios]

Hello,

 

Can some one help me with the following problem?

 

The first two lines calculate sucessfully, the first totals the column, the second calculates the vat. The next should add the two together but i cant get the correct figure!

 

Any pointers?

 

SELECT 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+amount * 0.175 ,2), amount)) AS toolsnet, 

 

Many thanks

[CtrlAltDel]

I know very little about this coding etc, but looking at it from a maths point of view, are you adding VAT twice?,once in toolvat and again in toolsnet.

Just a thought, i could be miles out.

[Barand]

try

 

ROUND(amount * 1.175 ,2)

 

for the last one

[nealios]

Thanks but its still not working.

 

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

 

any other suggestions?

 

[Barand]

my data

[pre]

id | amount | vatid

---+--------+-------

1  |  100  |  2

2  |  200  |  2

3  |  300  |  0

[/pre]

 

my query

 

SELECT SUM(ROUND(amount,2)) AS toolstotal,

SUM(IF(vatid='2',ROUND(amount * 0.175, 2), 0.00)) AS toolsvat,

SUM(IF(vatid='2',ROUND(amount * 1.175 ,2), amount)) AS toolsnet

FROM mytable

 

my results

[pre]

toolstotal |  toolsvat  |  toolsnet

-----------+------------+--------------

600.00    |  52.50    |  652.50

[nealios]

 

  Expense Type    Amount    VAT        NET

                    Tools      100.00    17.50    117.50

    Tools     100.00 17.50   117.50

    Tools     100.00 17.50   117.50

    Tools     100.00 17.50   117.50

    Tools     100.00 0.00   100.00

 

I have five columns. Where the vat id is selected to '2' it calculates the vat, where it is selected to '1' no vat is necessary. I dont know why mine wont work?

 

Results

 

    Total  Vat  Net

Tools 500.00 70.00 1320.00

 

"SELECT 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

[nealios]

Sorry the net figure reads 1070.00, so it seems to be doubling the amount.

 

 

[Barand]

You only show part of your query. Is there a join involved?

[nealios]

Yes there is a join.

 

I have a page which i can enter two dates and then the query searches for all records between those two dates.

 

SELECT 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), 0.00)) AS toolsnet
FROM expenditure, expenses WHERE expenditure.expenseid = expenses.expenseid AND `extransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "'";

 

many thanks

[Barand]

if the join matched 2 records in the other table you get a doubling effect

 

http://www.phpfreaks.com/forums/index.php/topic,174835.msg773695.html#msg773695

[nealios]

Thanks for the link, i had a look and i implemented the group by function. This enabled the calculation to work. I dont think the left join is relevant as i am only using the join to find the expensetype from the expenseid rather than taking amounts from both tables.

 

$sql1 = "
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 a couple of more lines it only calculates the first three. Ive tried group by expensetype but it only calculates the second set 'petrol'. I am miffed!

 

$sql1 = "
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";

[nealios]

Ive completly got rid of the join. I can calculate the first two column total and vat of both expenseid 3 and 4.

 

However the net is still not calculating properly. I think the syntax of the statement maybe wrong but i have nothing to compare it to. Does anyone have an alternative methods or anything they think i should try?

 

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

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

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