2

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

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 ;
| improve this answer | |
2

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;
| improve this answer | |
1

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;
| improve this answer | |
  • I can do like this but it increases complexity because their are so many other columns from different tables – Tanveer Jafri Dec 12 '18 at 10:53
  • ypercube has just been post the other option – McNets Dec 12 '18 at 10:55

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.