adding a column to a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adding a column to a query

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2011-06-14 : 06:19:58

Hi, I want to add a column and set it to a string value in a query.
The reason is so that I can distinguish between direct debits or credit cards in a union query. The direct debits can then be multiplied by 12.

Or better still how do I multiply a direct debit value by 12 then I don't need an extra column

eg


select myColumnIJustMadeUP as 'CREDITCARD'
union
select myColumnIJustMadeUP as 'DEBITCARD'
or better
select amount from CreditCard
UNION
select [amount * 12] from DebitCard

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-14 : 06:43:33

What's wrong with what you have?

select ...., myColumnIJustMadeUP as 'CREDITCARD'
from ...
union
select ..., myColumnIJustMadeUP as 'DEBITCARD'
from ..
or better
select ..., amount
from CreditCard
UNION
select ..., amount = amount * 12
from DebitCard

You should probably have union all rather than union.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-14 : 06:43:57

If CreditCard and DebitCard are two tables, then do it just the way you did - i.e.,


select amount from CreditCard
UNION
select amount*12 from DebitCard

If the amounts are in the same table, and another column in that table identifies the transaction as being Credit/Debit, then:


select
   case 
      when CreditOrDebit = 'Credit' then amount
      else amount*12
   end  as Amount
from
   YourTable

insanepaul
Posting Yak Master

178 Posts

Posted - 2011-06-14 : 07:04:28

quote:
Originally posted by nigelrivett

What's wrong with what you have?

select ...., myColumnIJustMadeUP as 'CREDITCARD'
from ...
union
select ..., myColumnIJustMadeUP as 'DEBITCARD'
from ..
or better
select ..., amount
from CreditCard
UNION
select ..., amount = amount * 12
from DebitCard

You should probably have union all rather than union.

Hi, error message says 'invalid column name' then the column name I made up which isn't in the table because I want to dynamically make it up for a report.

By the way the amount worked - dunno why it didn't first time around.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-14 : 07:13:02

You probably need this where the character string is aliased to the column name:


select 'CREDITCARD' as myColumnIJustMadeUP
union
select 'DEBITCARD' as myColumnIJustMadeUP as

insanepaul
Posting Yak Master

178 Posts

Posted - 2011-06-14 : 07:13:35

OK my mistake its select ...,'CreditCard' as CardType

Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources