snippetsqlMinor
How to use column alias to calculate other column value
Viewed 0 times
columnothervaluecalculatehowusealias
Problem
My query is
and im getting an error that unknown column earnings and deductions because these are column alias not column name ..
Any solution?
Thanks in advance
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
salaryand 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:
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:
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) varsDocumentation 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) varsWITH 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.