mysql variables in the select

Question

Can i use num_daysopen in the second select

SELECT
  DATEDIFF(DATE(NOW()), DATE(dateopened)) AS num_daysopen,
  (28 - DATEDIFF(DATE(NOW()), DATE(dateopened))) as days_left
FROM table

The following does not seem to work, it would be more readable than the above if it did?

SELECT
  DATEDIFF(DATE(NOW()), DATE(dateopened)) AS num_daysopen,
  (28 - num_daysopen) as days_left
FROM table

gbn · Accepted Answer · 2012-02-09 11:54:48Z

2

A column alias isn't available for use at the same level.
So add a derived table to add a level

SELECT
   num_daysopen,
   (28 - num_daysopen) as days_left
FROM
   (
    SELECT 
       DATEDIFF(DATE(NOW()), DATE(dateopened)) AS num_daysopen
    FROM 
       table
   ) T

See "Problems with Column Aliases" in the MySQL Docs

edited Feb 9, 2012 at 11:54

answered Feb 9, 2012 at 11:49

gbn

434k84 gold badges602 silver badges689 bronze badges

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

1 Comment

Robbo_UK Over a year ago

thanks for this, it answers the question and provides an alternative solution.

dgw · Accepted Answer · 2012-02-09 11:52:22Z

2

As stated in the docs

A select_expr can be given an alias using AS alias_name. The alias is used as the
expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.

The alias cannot be used in select_expr or in the WHERE clause.

answered Feb 9, 2012 at 11:52

dgw

13.7k11 gold badges60 silver badges56 bronze badges

Comments

Jivings · Accepted Answer · 2012-02-09 11:49:00Z

0

The alias set with AS is used as the expression's column. It can't be used as a variable, but it can be used again to reference that column.

answered Feb 9, 2012 at 11:49

Jivings

23.3k7 gold badges63 silver badges102 bronze badges

Collectives™ on Stack Overflow

mysql variables in the select

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related