How to create query with column computation based on condition from other column

Question

0.00/5 (No votes)

See more:

Hi I have a SQL table:

SQL

SrNo Name     Amount  ApplyTax
1    John     500      Yes 
2    Peter    700      No
3    Tom      900      Yes

If I want to levy tax at 20% on those who have ApplyTax as 1, how do I do that in one query:

The output I need is

XML

SrNo Name     Amount  Tax
1    John     500      100
2    Peter    700      0
3    Tom      900      180

I am new to SQL so any help would be great.

Thanks,
T

Posted 18-Mar-15 20:41pm

3 solutions

SQL

select SrNo,name ,Amount,case when ApplyTax='Yes' then (Amount*20)/100 when ApplyTax='No' then 0 end as Tax From table_name

Posted 18-Mar-15 20:48pm

Try:

SQL

SELECT SrNo, Name, Amount, 
   CASE WHEN ApplyTax='Yes' THEN (Amount * 20) / 100 ELSE 0       
   END AS Tax 
FROM MyTable

Posted 18-Mar-15 20:50pm

Add a Solution

Treat my content as plain text, not as HTML

…

Sign in to your account

Download, Vote, Comment, Publish.

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajesh waran · Accepted Answer · 2015-03-18T20:46:00

Try this,

SQL

select SrNo,Name,Amount,case when ApplyTax='Yes' then (Amount*0.2) else 0 end as Tax  from Table

Here I have used (Amount*0.2), which is equal to 20% of Amount.

SQL

20% Amount = (Amount/100)*20

Posted 18-Mar-15 20:46pm

Updated 18-Mar-15 20:48pm

v3