CODEWITHSUNDEEP
oracle-databasesumsubquery
llanato
llanato

Reputation: 2491

Oracle SUM calculated values

I'm trying to figure out how I can or if it's possible to sum the returned values from columns on the fly to have a total value returned for each record instead of having to calculate it again.

The query has multiple sub-queries which all calculate correctly and return an integer value, I'm trying to find a way to add these values on the fly into a Total column, is it even possible!?

SELECT (SELECT value FROM ....) AS NUM_1
, (SELECT value FROM ....) AS NUM_2
, (SELECT value FROM ....) AS NUM_3
, SUM(NUM_1 + NUM_2 + NUM_3) AS TOTAL
FROM DUAL;

This is the output I'm looking to achieve:

NUM_1 | NUM_2 | NUM_3 | Total
=============================
3     | 4     | 3     | 10

Upvotes: 0

Views: 1096

Answers (2)

Jatin
Jatin

Reputation: 1

Remove semi colon (;) after dual and then run as whole it will work

with numbers as ( SELECT (SELECT value FROM ....) AS NUM_1, (SELECT value FROM ....) AS NUM_2, (SELECT value FROM ....) AS NUM_3 FROM DUAL ) select num_1, num_2, num_3, num_1 + num_2 + num_3 as total from numbers;

Upvotes: -1

user330315
user330315

Reputation: 

with numbers as (
  SELECT (SELECT value FROM ....) AS NUM_1,
         (SELECT value FROM ....) AS NUM_2,
         (SELECT value FROM ....) AS NUM_3
  FROM DUAL;
) 
select num_1, 
       num_2, 
       num_3, 
       num_1 + num_2 + num_3 as total
from numbers;

You have to make sure that your (SELECT value FROM ....) queries return exactly one row otherwise you'll get an error like "Single row sub-query returns multiple rows".

Upvotes: 2

Related Questions