CODEWITHSUNDEEP
mysql
Anthony
Anthony

Reputation: 37065

MySQL - reference dynamic column in second dynamic column

I have a table like this:

user | test_score
  a  |  50
  b  |  60

And I need query results that has a column for 1.5 times the test score and then the difference of that from the original. The results would be:

user | test_score | adjusted_test_score | test_score_diff
  a  |  50        |            75       |   25
  b  |  60        |            90       |   30

The query I tried:

 SELECT
 user AS user,
 test_score AS test_score,
 (test_score * 1.5) AS adjusted_test_score,
 (adjusted_test_score - test_score) AS test_score_diff

Returns an error that 'adjusted_test_score' column does not exist.

Is there a way to reference this column without doing a join?

Upvotes: 1

Views: 418

Answers (2)

extraplanetary
extraplanetary

Reputation: 194

Why not just repeat yourself?

SELECT user AS user, test_score AS test_score, (test_score * 1.5) AS adjusted_test_score, ((test_score * 1.5) - test_score) AS test_score_diff

Upvotes: 1

Xint0
Xint0

Reputation: 5399

Try:

SELECT user, test_score, adjusted_test_score,
    (adjusted_test_score - test_score) AS test_score_diff
FROM (
    SELECT user, test_score, (test_score * 1.5) AS adjusted_test_score
    FROM source_table
)

Or you can also do this:

SELECT user, test_score, (test_score * 1.5) AS adjusted_test_score,
    ((test_score * 1.5) - test_score) AS test_score_diff
FROM source_table

Upvotes: 1

Related Questions