accessing vitual fields from table

[Shashis]

Select sum(qty1) as col1,sum(qty2) as col2, sum(qty3) as col3 from table1

I want to access the virtual columns col1, col2, col3 in the query something
like


Select sum(qty1) as col1,sum(qty2) as col2, sum(qty3) as col3, (col1 + col2
+ col3 ) from table1
But this does not work.

Please help. Thanks in advance.

 

[Marshall Barton]

Select sum(qty1) as col1,sum(qty2) as col2, sum(qty3) as col3 from table1

I want to access the virtual columns col1, col2, col3 in the query something
like


Select sum(qty1) as col1,sum(qty2) as col2, sum(qty3) as col3, (col1 + col2
+ col3 ) from table1

Repeat the sum expressions instead of using the alias names:

...., sum(qty1) + sum(qty2) + sum(qty3) as Total

 

[KARL DEWEY]

You should try it rather than asking.
About 90% of time it will not but this should --
SELECT sum(qty1) as col1,sum(qty2) as col2, sum(qty3) as col3, (sum(qty1) +
sum(qty2) + sum(qty3)) SumOfAll
FROM table1

 

[John W. Vinson]

Select sum(qty1) as col1,sum(qty2) as col2, sum(qty3) as col3 from table1

I want to access the virtual columns col1, col2, col3 in the query something
like


Select sum(qty1) as col1,sum(qty2) as col2, sum(qty3) as col3, (col1 + col2
+ col3 ) from table1
But this does not work.

Please help. Thanks in advance.

You (usually) can't reuse a calculated field in a further calculation; you
must instead recapitulate the calculated expression:

Select sum(qty1) as col1,sum(qty2) as col2, sum(qty3) as col3, (sum(qty1) +
sum(qty2)+ sum(qty3) ) AS GrandTotal from table1

I'm pretty sure the query optimizer is smart enough to actually traverse the
table only once.