How to use column alias to calculate other column value

Question

My query is

SELECT 
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings 
    ,(`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions 
   ,(earnings - deductions) AS net_salary 
FROM 
    salary

and im getting an error that unknown column earnings and deductions because these are column alias not column name .. Any solution? Thanks in advance

Answer 1

Additionally to previous answers.

1) You can try to use user-defined variables:

SELECT 
    @earnings := (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings ,
    @deductions := (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions,
    @earnings - @deductions AS net_salary 
FROM 
    salary
    -- , (SELECT @earnings:=0, @deductions:=0) vars

Documentation do not guarantee the calculations order, but in practice all calculations are performed from left to right.

2) If your MySQL version is 8+, you can use CTE:

WITH cte AS (
SELECT 
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings, 
    (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions 
FROM 
    salary
)
SELECT earnings, deductions, (earnings - deductions) AS net_salary
FROM cte;

Answer 2

You cannot use an alias in the same SELECT list.

You can either repeat the expressions:

SELECT 
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) 
        AS earnings , 
    (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`)
        AS deductions ,
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) 
    -
    (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`)
        AS net_salary
FROM salary ;

or use a derived table:

SELECT 
    earnings , 
    deductions ,
    earnings - deductions AS net_salary
FROM 
  (
    SELECT 
        (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) 
            AS earnings , 
        (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`)
            AS deductions
    FROM salary 
  ) AS dt ;

Answer 3

Replace alias with the computed columns.

SELECT 
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings 
    ,(`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions 
    ,(`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) - (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`)  AS net_salary 
FROM 
    salary;