DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Combine columns from two select statements to one output table (join not possible!)

  1. #1
    Join Date
    Dec 2012
    Posts
    3

    Combine columns from two select statements to one output table (join not possible!)

    Combine columns from two select statements to one table

    1. statement
    Select Sum (Brutto) as B1, Sum (Netto) as N1
    FROM Table 1
    where ORDER_DATE between '19.09.11' and '19.12.11'

    2. statement
    Select Sum (Brutto) as B2, Sum (Netto) as N2
    FROM Table 1
    where ORDER_DATE > trunc(add_months(sysdate,-3))

    Example for output statement 1
    B1 N1
    1658,89 1522,12

    Example output statement 2
    B2 N2
    3256,45 2358,56

    Question: how to combine both statements into one output?
    B1 N1 B2 N2
    1658,89 1522,12 3256,45 2358,56

    Note: the two resulting tables do not have a column in common; thus one cannot use use a join - correct?

    Thanks for your help!

  2. #2
    Join Date
    Dec 2012
    Posts
    3
    Possible Solution

    Select t1.B1, t1.N1, t2.B2, t2.N2
    From
    (Select Sum (Brutto) as B1, Sum (Netto) as N1
    FROM Table 1
    where ORDER_DATE between '19.09.11' and '19.12.11') t1
    ,
    (Select Sum (Brutto) as B2, Sum (Netto) as N2
    FROM Table 1
    where ORDER_DATE > trunc(add_months(sysdate,-3))) t2

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    If you are trying to get the rows from both tables,
    but the data is different then you want a union all.

    Code:
    select *
      from ( select sum (brutto) as b1, sum (netto) as n1
               from table 1
              where order_date between '19.09.11' and '19.12.11'
              union all
             select sum (brutto) as b2, sum (netto) as n2
               from table 1
              where order_date > trunc(add_months(sysdate,-3)))
     order by 1;

  4. #4
    Join Date
    Dec 2012
    Posts
    3

    different visualisation

    Thanks for the response.

    Note: the main difference between simply using a comma (1) or union all (2) is visualisation of the resulting table, i.e.

    (1) present all four columns from the two resultnig tables side by side (= resulting table with 4 columns and 1 row), while

    (2) presents results from one table in one column and results from the second result table in a second column (= resulting table with two columns and two rows).

  5. #5
    Join Date
    Oct 2013
    Location
    Bangalore
    Posts
    1
    Hiii Kairos,

    Did you happen to find a solution for your query?
    I have the same query and need help, could you help me?

    Thanks,
    Dharini

  6. #6
    Join Date
    Apr 2014
    Posts
    2
    WITH
    query1 as (
    Select nvl(Sum (Brutto),0) as B1, nvl(Sum (Netto),0) as N1
    FROM Table 1
    where ORDER_DATE between '19.09.11' and '19.12.11'
    ),
    query1 as (
    Select nvl(Sum (Brutto),0) as B2, nvl(Sum (Netto),) as N2
    FROM Table 1
    where ORDER_DATE > trunc(add_months(sysdate,-3))
    )
    select B1,N1,B2,N2 from query1, query2;

    suppose that in the result always is going to be an element in any result of any query (nvl) and only one row (not using a group by), using a full join is going to give you what you are looking for.

  7. #7
    Join Date
    Apr 2014
    Posts
    2
    Quote Originally Posted by hujasodu View Post
    WITH
    query1 as (
    Select nvl(Sum (Brutto),0) as B1, nvl(Sum (Netto),0) as N1
    FROM Table 1
    where ORDER_DATE between '19.09.11' and '19.12.11'
    ),
    query1 as (
    Select nvl(Sum (Brutto),0) as B2, nvl(Sum (Netto),) as N2
    FROM Table 1
    where ORDER_DATE > trunc(add_months(sysdate,-3))
    )
    select B1,N1,B2,N2 from query1, query2;

    suppose that in the result always is going to be an element in any result of any query (nvl) and only one row (not using a group by), using a full join is going to give you what you are looking for.

    query1 as (
    Select nvl(Sum (Brutto),0) as B1, nvl(Sum (Netto),0) as N1
    FROM Table 1
    where ORDER_DATE between '19.09.11' and '19.12.11'
    ),
    query2 as ( -- <---- it was query2.
    Select nvl(Sum (Brutto),0) as B2, nvl(Sum (Netto),) as N2
    FROM Table 1
    where ORDER_DATE > trunc(add_months(sysdate,-3))
    )
    select B1,N1,B2,N2 from query1, query2;

    correction

  8. #8
    Join Date
    Jan 2018
    Location
    United States
    Posts
    1
    hujasodu, Thanks! That's what i've looking for a long time Wish all the best for You

  9. #9
    Join Date
    Nov 2018
    Posts
    3
    Wow! Thanks for the solution. I was searching for this for a while.

  10. #10
    Join Date
    Sep 2020
    Posts
    4
    nice elegant solution

Posting Permissions

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


Click Here to Expand Forum to Full Width