Solved

calculation in select

Posted on 2006-06-21
7
1,432 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:ANAT2403
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16952040
select  amount,
     amount * (VAT / 100) as vatsum,
     amount + (amount * (VAT / 100)) as total
from table1
0
 
LVL 5

Expert Comment

by:MageDribble
ID: 16952086
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
 
LVL 5

Expert Comment

by:MageDribble
ID: 16952102
if amount is indexed you may return faster results this way.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:ANAT2403
ID: 16952533
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 Comment

by:ANAT2403
ID: 16952632
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
 
LVL 5

Accepted Solution

by:
rmacfadyen earned 500 total points
ID: 16952694
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
 

Author Comment

by:ANAT2403
ID: 16952767
Thankyou you are write as you can see in my previuos answer I didn't show the complete query and there was the
problem that I already solved.
Anat
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL server 2012 BPA and Baseline Configuration Analyzer 2.0 16 32
SQL Server Sum Over Multiple Tables 20 35
SQL Distinct Question 3 15
Applying Roles in Common Scenarios 3 19
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question