[SOLVED] Help with IF Select

[nealios]

Hello,

 

I am trying calculate vat on certain records in my table. At the moment i have the query half working and can perform my calculations where the vattype is set to yes.

 

But if the vattype is set to 'no' i want the vat alias to appear as NULL. I was thinking something like this but im not sure how to display it. 

 

IF(vattype='yes',ROUND(amount * 7/47, 2),amount), IF(vattype='no',ROUND(null) AS vat

 

My actual query

 

SELECT incomeid, intransactiondate, invoiceno, amount, IF(vattype='yes',ROUND(amount * 7/47, 2),amount)  AS vat, IF(vattype='yes',ROUND(amount-(amount* 7/47), 2),amount) AS net FROM `income`, `vat` WHERE income.vatid = vat.vatid;

 

 

Many thanks

[toplay]

You can't round(NULL). It can be zero or NULL, you pick. But since it a number I would say zero. So, you have to change the 'amount' column after the comma to what you want...example:

 

IF(vattype = 'yes', ROUND(amount * 7/47, 2), 0.00) AS vat

 

or

 

IF(vattype = 'yes', ROUND(amount * 7/47, 2), NULL) AS vat

 

 

[nealios]

Thanks for your reply i came over that part of the problem by

 

IF(vattype='no',ROUND(amount-(amount), 2),amount) AS vat,

 

 

However its still not doing exactly what i want the query to do. Im trying to display all the records in the table. If vattype is set to yes it should calculate vat on top.

If the vattype is set to no it only needs to display the record but it only needs to be displayed as blank.

 

At the moment the two if statements work individually but not together as below. I want to combine the first two if statements into one but not sure how it could be written.

 

$sql = "SELECT incomeid, intransactiondate, invoiceno, amount, 
IF(vattype='yes',ROUND(amount * 7/47, 2),amount) AS vat, 
IF(vattype='no',ROUND(amount-(amount), 2),amount) AS vat, 
IF(vattype='yes',ROUND(amount-(amount* 7/47), 2),amount) AS net 

FROM `income`, `vat` WHERE income.vatid = vat.vatid";

[toplay]

You're making it more difficult than it needs to be. You have: amount - (amount) which is zero. So, just do what I already showed in my previous post.

 

If you don't want to display something when it's zero, then change your query to only retrieve vat's that's greater than (or not) zero (also can use PHP to determine to display something or not).

 

[nealios]

Thanks for your help it works! Its taken me an age!

 

Many thanks

[nealios]

Im now trying to add up the total amount of each table column. But only the first line works.

 

Can anyone solve this problem?

 

SELECT Round(SUM(amount),2) as total, 
IF(vattype = 'yes', ROUND(sum(amount * 7/47, 2), 0.00) AS totalvat,
IF(vattype='yes',ROUND(sum(amount-(amount* 7/47), 2),amount) AS totalnet 


FROM `income`, `vat` WHERE income.vatid = vat.vatid AND `intransactiondate` BETWEEN '" . $date1 . "' AND '" . $date2 . "' order by $sort";

[fenway]

First line/

[nealios]

Sorry i dont understand?

 

I know the first line works but was struggling to get it to work with the IF.

[toplay]

Put the sum() function on the outside...example:

 

SUM(IF(vattype = 'yes', ROUND(amount * 7/47, 2), 0.00)) AS totalvat