# calculation in select

In sql server 2000 I have the following query in a stored procedure:
select  amount,
amount * VAT / 100 as vatsum,
amount + (amount * VAT / 100) as total
from table1

1. if the value of VAT is 0 then the value of total also become 0 although it is not 0 because it suppose to add the
amount. What is the problem here.
2. I wanted to write it like this
select        amount,
amount * VAT / 100 as vatsum,
amount + vatsum  as total
from table
but it is not accepted . what is the problem and how do I write it so that I write the calculation of vatsum only once?
Thankyou
Anat
###### Who is Participating?

Commented:
Are you certain of your problem? With a VAT of 0 there I would be hugely surpised if the total as zero (amount + (amount * 0 / 100)). Here's a hardcoded sample that shows the basic math works as expected:

declare @Amount decimal(8,2)
declare @Vat int

set @Amount = 10.23
set @Vat = 7

select @Amount, round(@Amount * @Vat / 100, 2), round(@Amount + (@Amount * @Vat / 100), 2)

set @Amount = 10.23
set @Vat = 0

select @Amount, round(@Amount * @Vat / 100, 2), round(@Amount + (@Amount * @Vat / 100), 2)

Are you perhaps not showing your full query? If you are not... have you considered how NULL will affect numeric calculations (SUM() and so forth)?

Regards,

Rob
0

Billing EngineerCommented:
select  amount,
amount * (VAT / 100) as vatsum,
amount + (amount * (VAT / 100)) as total
from table1
0

Commented:
Whether you write is as "amount + vatsum" or "amount + (amount...."  It would still calculate vatsum twice b/c vatsum is just an alias to that column.  SQL Server would need to calculate it twice.

you could re-write it to eliminate the multiple calculations.  Try the below query and see if it returns faster results.

select  amount,
amount * VAT / 100 as vatsum,
amount + (amount * VAT / 100) as total
from table1
where amount <> 0

UNION ALL select  0,
0,
0
from table1
WHERE amount = 0
0

Commented:
if amount is indexed you may return faster results this way.
0

Author Commented:
nobody solved  the problem. the problem is not the fast of the query but that the total return 0 althogh in consists of
the amount which is not 0 and the vatsum which is 0
Can you help?
Anat
0

Author Commented:
Ok  sorry, I found it. it's my mistake .
actually the VAT was written like this:
ISNULL(VAT, 0) and I didn't write it in the second part.
Sorry
Thankyou all
Anat
0

Author Commented:
Thankyou you are write as you can see in my previuos answer I didn't show the complete query and there was the