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

Can you please tell how you expect result look like?

Ivan Nevostruev
– Ivan Nevostruev

2009-10-08 22:34:52 +00:00
Commented Oct 8, 2009 at 22:34 — Ivan Nevostruev
– Ivan Nevostruev, Commented Oct 8, 2009 at 22:34

Quassnoi · Accepted Answer · 2009-10-09 12:18:41Z

1

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

answered Oct 9, 2009 at 12:18

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

code_burgar · Accepted Answer · 2009-10-08 22:29:21Z

0

Try with:

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

answered Oct 8, 2009 at 22:29

code_burgar

12.3k4 gold badges37 silver badges53 bronze badges

1 Comment

Steve Over a year ago

That looks like what I need. Thanks. Is there a way to get the amount in to one column after? Thanks for posting the code as well, I'm new to this and it really helps.

Cory House · Accepted Answer · 2009-10-09 12:09:20Z

0

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

edited Oct 9, 2009 at 12:09

answered Oct 8, 2009 at 22:26

Cory House

15.2k13 gold badges75 silver badges89 bronze badges

1 Comment

Steve Over a year ago

That looks like what I need. Thanks. Is there a way to get the amount in to one column after?

Collectives™ on Stack Overflow

Using Alias with MySql

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related