Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: I need select: total sum from two tables

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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



    Originally posted by ARWinner
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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


    Originally posted by sathyaram_s
    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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Sorry, missed it ..

    Thansk for pointing out

    Sathyaram

    Originally posted by ARWinner
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    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

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    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

  8. #8
    Join Date
    Oct 2003
    Posts
    706

    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...
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •