HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to use column alias to calculate other column value

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
columnothervaluecalculatehowusealias

Problem

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

Solution

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;

Code Snippets

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
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;

Context

StackExchange Database Administrators Q#224746, answer score: 3

Revisions (0)

No revisions yet.