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

[Kairos2012]

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!

[Kairos2012]

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

[gandolf989]

If you are trying to get the rows from both tables,
but the data is different then you want a union all.

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;

[Kairos2012]

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).

[Dharini]

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

[hujasodu]

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.

[hujasodu]

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

[mariapcsoowa]

hujasodu, Thanks! That's what i've looking for a long time Wish all the best for You

[Preethi56]

Wow! Thanks for the solution. I was searching for this for a while.

[joshtuck]

nice elegant solution