Hi,

I have two tables with I have to make summary select statement.

Table1:
Sales
1
2

Table2:
Sales
3
4

I would like to write one SQL withc would return the sum from bouth tables that is: 10.

How to write such SQL?

Thanks,
Grofaty

Grofaty,

How about something like:

with temp1 (sales) as
(select sales from table1 union all select sales from table2)
select sum(sales) from temp1

Andy

Or

with temp1(sumsales) as
(
select sum(sales) from tabl1
union all
select sum(sales) from table2
)
select sumsales from temp1

I think this will make a difference if the table is large ... Probably it may not require to create a large temp table ...

(This is just my thought ... Untested one)

Cheers
Sathyaram

You will still need to sum the sums:

with temp1(sumsales) as
(
select sum(sales) from tabl1
union all
select sum(sales) from table2
)
select sum(sumsales) from temp1

Andy

Sorry, missed it ..

Thansk for pointing out

Sathyaram

Hi,

Thanks to you all.
I have one more sample. When I wrote my post I was just forgeting to write "as whatever" as temporaly name.

Sample - this works
select sum(Sales) from
(select Sales from table1 union all select Sales from table2)
as whatever;

Sample - this does't works
select sum(Sales) from
(select Sales from table1 union all select Sales from table2)
;

Thanks,
Grofaty

Hi,

I have also created access plan for all 3 SQL variants (ARWinner, sathyaram_s, grofaty).

The access plan was the same for all 3 SQL variants! Total cost for all variants were 50.1767 so there is no difference in performances.

I think that DB2 optimizer rewrites SQL...

Grofaty

That is precisely what the optimizer does. The SQL command is a description of what the result should be, and the optimizer's job is to decide .. on the fly, as it were .. how to get it.