CODEWITHSUNDEEP
sqloracle-databaseoracle11g
WOPR
WOPR

Reputation: 319

summing columns in a row in Oracle

How would I do something like this in PL/SQL (Oracle 11)?

select 
  1 as aa,
  2 as bb,
  3 as cc,
  (aa + bb + cc) as total
from my_table;

to yield this:

-------------------------
aa   bb   cc     total
-------------------------
1    2    3      6
-------------------------

Upvotes: 0

Views: 74

Answers (4)

user5683823
user5683823

Reputation:

If I understand your question correctly, you can't - not in the way you wrote it. You can't reference column aliases in building another column in the same SELECT (at the same level). Instead of aa + bb + cc, you will have to repeat 1, 2, 3.

To clarify, I believe what's behind your question is something like this:

select   deptno, max(salary) as max_s, min(salary) as min_s, (max_s + min_s)/2 as mid_s
from     scott.emp
group by deptno

(trying to find the midrange salary by department, using the EMP table in the standard SCOTT schema, present in most Oracle database installations)

You can't do that - you will have to repeat max(salary) and min(salary) in the formula for the last column, or else you will need a subquery and an outer query. In almost all cases, the first choice, not the second, will be more efficient (even though it require more writing - and it may be harder to maintain).

Upvotes: 0

Digital Alchemist
Digital Alchemist

Reputation: 2332

We Must Also handle Null Values as it will garbage out the result

SELECT (temp.a + temp.b + temp.c) sumTemp FROM (
  SELECT NVL(a,0) as a, NVL(b,0) as b, NVL(c,0) as c  FROM DUAL 
) temp

Upvotes: 1

ChrisB
ChrisB

Reputation: 2585

select aa,
       bb,
       cc,
       aa + bb + cc as total
from
(
  select 1 as aa,
         2 as bb,
         3 as cc
  from dual
)

Upvotes: 0

sagi
sagi

Reputation: 40491

Is this what you need?

SELECT aa,bb,cc,aa+bb+cc as total 
FROM (
    SELECT 1 as aa,
           2 as bb,
           3 as cc
    FROM My_Table)

If so, I don't understand why use My_Table and not dual..

Upvotes: 1

Related Questions