Using Alias with MySql

Question

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 :-)

Answer 1

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 ...

Answer 2

Try with:

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

Answer 3

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