CODEWITHSUNDEEP
sql
IronMan3000
IronMan3000

Reputation: 27

Add select statements

I am learning to use SQL and got the problem that I cant find a solution to add two select statements.

I tried it with union and the function sum(). I also tried to find a similar question here - without success.

select *
from (select 6,4,2,4,7,2,7
      from dual
      union
      select 3,8,9,2,7,4,5
      from dual)

I tried this but it shows me two rows with the numbers in the code. I want the result of the rows in one single row, like:

9,12,11,6,14,6,12

Upvotes: 0

Views: 53

Answers (2)

forpas
forpas

Reputation: 164099

You must alias the columns of the 1st query, and use sum() to aggregate on each of the columns:

select 
  sum(col1) sum1, sum(col2) sum2, sum(col3) sum3, sum(col4) sum4, sum(col5) sum5, sum(col6) sum6, sum(col7) sum7
from (
  select 6 col1, 4 col2, 2 col3, 4 col4, 7  col5, 2 col6, 7  col7 from dual 
  union 
  select 3, 8, 9, 2, 7, 4, 5 from dual
)

See the demo.
Results:

SUM1 | SUM2 | SUM3 | SUM4 | SUM5 | SUM6 | SUM7
---: | ---: | ---: | ---: | ---: | ---: | ---:
   9 |   12 |   11 |    6 |   14 |    6 |   12

Upvotes: 1

Himanshu
Himanshu

Reputation: 3970

First of all Union doesnt add data but combine rows and that too when the data to be unioned is having same no of columns and same type.

Your query is irrelevant and adding numerics like this is a bad practice and is off logic.

 1. Select 6+3,....,.. from table

 2. Select col1+col2 from table 
   where col1 in (6,4,2,4,7,2,7) and col2 in
   (3,8,9,2,7,4,5)

As you could see the second query makes sense but first query doesnt.

Upvotes: 0

Related Questions