Doing arithmetic on alias in SQL

Question

In my SQL query I need to do some arithmetic on alias.

SELECT 
    MY_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME1, 
    MY_SECOND_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME2,  
    MYALIASNAME1 - MYALIASNAME2 AS MYALIASHNAME3
FROM 
    MYTABLE

However, this does not work because it is not treating MYALIASNAME1 and MYALIASNAME2 as columns. Any ideas how can I achieve this?

I am using H2, specifically h2-1.3.173.jar. I am using it in server mode.

Thanks.

http://stackoverflow.com/questions/6081436/how-to-use-alias-as-field-in-mysql pls have look is it making any sense to u. — Zakaria, Nov 19 '13 at 05:39
Please tell us what **concrete database system** this is for - many things are vendor-specific. Are you using MySQL? Postgres? SQL Server? Oracle? IBM DB2? Something else entirely? Please update your tags to show what database system (and which version of it!) you're using - thanks! — marc_s, Nov 19 '13 at 05:56

score 3 · Accepted Answer · answered Nov 19 '13 at 05:40

try this.

SELECT X.MYALIASNAME1 - X.MYALIASNAME2 AS MYALIASNAME3 FROM
(
    SELECT 
        MY_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME1, 
        MY_SECOND_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME2

    FROM 
        MYTABLE
)X

score 1 · Answer 2 · edited Oct 26 '21 at 06:59

1

Use Common Table Expressions:

with cte as (
    select MY_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME1, 
        MY_SECOND_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME2
        
    from table
)
    
select MYALIASNAME1 - MYALIASNAME2 AS MYALIASHNAME3 from cte

edited Oct 26 '21 at 06:59

Costantino Grana

3,132
1
15
35

answered Nov 19 '13 at 05:38

vhadalgi

7,027
6
38
67

score 1 · Answer 3 · answered Nov 19 '13 at 05:41

Don't use the alias

SELECT 
   MY_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME1, 
   MY_SECOND_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME2,  
   MY_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN  - MY_SECOND_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASHNAME3
FROM 
    MYTABLE

Or use an intermediate step

SELECT MYALIASNAME1, MYALIASNAME2,  MYALIASNAME1 - MYALIASNAME2 AS MYALIASHNAME3
FROM  (

   SELECT 
       MY_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME1, 
       MY_SECOND_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME2
   FROM 
       MYTABLE
 ) TableAlias

score 0 · Answer 4 · answered Nov 19 '13 at 08:03

it will work...

"SELECT 
    MY_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME1, 
    MY_SECOND_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN AS MYALIASNAME2,  
    (MY_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN) - (MY_SECOND_COMPLEX_EXPRESSION_USING_SUM_AND_CASEWHEN) AS MYALIASHNAME3
FROM 
    MYTABLE"

Doing arithmetic on alias in SQL

4 Answers4