CODEWITHSUNDEEP
sqlmysql
Steve
Steve

Reputation: 51

Using Alias with MySql

I want to add an amount to the rows returned from a select. I've been trying things along the lines of:

select *,
       3 as amount
  from products
 where etc....

...and it works. However, I want to do the same thing for lots of rows in one go along the lines of:

select *,
       3 as amount,
       2 as amount,
       4 as amount
  from products 
 where id in ('1','2','3')

However this keeps adding amount columns and not changing the values in each row returned.

The amount is really an amount the users wants, it could be 1-99-4-2 or any number. I wanted to get a table with the results like: products amount --------------------------- ... 1 ... 99 ... 4 ... 2 I just wanted all the mount in one column thats why I was using select ? as amount select ? as amount but it just doesn't seem to work that way :-)

Upvotes: 0

Views: 153

Answers (3)

Quassnoi
Quassnoi

Reputation: 425823

SELECT  id, ELT(id, 3, 2, 4) AS amount
FROM    products 
WHERE   id IN ('1', '2', '3')

Upvotes: 1

Cory House
Cory House

Reputation: 15055

Give each alias a unique name. For example, amount1, amount2, etc.

EDIT> If you'd like sum of the columns, use SELECT SUM(amount1, amount2, amount3, ...) FROM ...

Upvotes: 0

code_burgar
code_burgar

Reputation: 12323

Try with:

SELECT *, 3 AS amt1, 2 AS amt2, 4 AS amt3 FROM products WHERE id IN ('1','2','3')

Upvotes: 0

Related Questions