0

In MySQL I know I can reuse an alias in a query like this:

SELECT
  value_a + value_b as value_sum,
  (SELECT value_sum) + 5 as padded_sum

Is there any way to make this work if padded_sum is also the name of an existing column in one of the joined tables?

http://sqlfiddle.com/#!9/48c2c9/6

1

For that you need user defined variables

SELECT
  item_id,
  @sum1 := value_a + value_b as value_sum,
  CONCAT('VALUE SUM: ', @sum1 + 5) as label,
  @sum2 := value_a + value_b as value_sum2,
  CONCAT('VALUE SUM: ', @sum2) as label2
 FROM item i
   LEFT JOIN item_sums isu USING (item_id);

Result

item_id     value_sum   label           value_sum2  label2
1           4           VALUE SUM: 9    4           VALUE SUM: 4
2           5           VALUE SUM: 10   5           VALUE SUM: 5
3           11          VALUE SUM: 16   11          VALUE SUM: 11

sample

  SELECT 
      item_id,
      value_sum,
      CONCAT('VALUE SUM: ', value_sum + 5) as label,
      value_sum2,
      CONCAT('VALUE SUM: ', value_sum2) as label2
  FROM
  (SELECT
      item_id,
      value_a + value_b as value_sum,
      value_a + value_b as value_sum2
   FROM item i
       LEFT JOIN item_sums isu USING (item_id)) t1;

sample2

| improve this answer | |
  • thanks, I am aware of this approach but can it be done without using variables? – billynoah Oct 7 at 0:44
  • yes, but it only gets uglier see changed answer, if you don't want to use the sum again – nbk Oct 7 at 1:03
  • oh no ..lol that's not what I meant at all. but thanks anyway. – billynoah Oct 7 at 1:17
  • what do you mean? there are no other possibilities, to use value_sum and this is more than enough – nbk Oct 7 at 1:19
  • no that is not what you ask. but for an answer you can identify every table and column by simple write table1.value_a or table2.value_a, with a dot between you can combine databse table and column – nbk Oct 7 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.