CODEWITHSUNDEEP
sqloracle-database
Americo
Americo

Reputation: 919

SQL Oracle- Combining Two Result Sets as one

I have the following queries:

Select TRUNC(create_dtime) as Day, Count(Create_Dtime) As SinS_Tot
  From player_chkin_hist
 Where Product_Id Is Not Null
   And Trunc(Create_Dtime) >= To_Date('2012-sep-01','yyyy-mon-dd')
   And Trunc(Create_Dtime) < To_Date('2012-sep-19','yyyy-mon-dd')
 Group By Trunc(Create_Dtime)
 Order By 1 Asc

and

Select TRUNC(Create_Dtime) As Day, Count(Create_Dtime) As Sins_Tot
  From Player_Chkin
 where Product_Id Is Not Null
   And Trunc(Create_Dtime) >= To_Date('2012-sep-01','yyyy-mon-dd')
   And Trunc(Create_Dtime) < To_Date('2012-sep-19','yyyy-mon-dd')
 Group By Trunc(Create_Dtime)
 Order By 1 Asc

Query 1 Results in:

Day          SinS_Tot
01-Sep-12      10000
02-Sep-12       9000

Query Two Results in:

 Day          SinS_Tot
03-Sep-12      11500
04-Sep-12       9800

Is there an easy way to combine these two queries so that I see all data? I thought about using a join, but since the dates do not overlap, there would be nothing to set the inner join equal on.

Upvotes: 0

Views: 4697

Answers (4)

gustavodidomenico
gustavodidomenico

Reputation: 4681

You just need to use the UNION group operator.

[Query1] UNION ALL [Query2]

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231851

You can UNION ALL the two results

<<query 1>>
UNION ALL
<<query 2>>

You could also use a UNION but that would be more expensive since it would force Oracle to look for and eliminate duplicate rows. If you know that the two queries will never return the same row, or if you would want both rows, this is unnecessary overhead.

Upvotes: 1

Yaroslav
Yaroslav

Reputation: 6554

Maybe a UNION between them? Keep the ORDER BY outside the queries, put it at the end of the second one

Query1
 UNION
Query2

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

What you're looking for is a UNION (Or UNION ALL, depending on if you want to elminate duplicates or not):

Select 
trunc(create_dtime) as Day, Count(Create_Dtime) As SinS_Tot
From player_chkin_hist
Where
Product_Id Is Not Null And
Trunc(Create_Dtime) >= To_Date('2012-sep-01','yyyy-mon-dd')
And Trunc(Create_Dtime) < To_Date('2012-sep-19','yyyy-mon-dd')
Group By Trunc(Create_Dtime)

UNION

Select Trunc(Create_Dtime) As Day, Count(Create_Dtime) As Sins_Tot
From Player_Chkin
where
Product_Id Is Not Null And
Trunc(Create_Dtime) >= To_Date('2012-sep-01','yyyy-mon-dd')
And Trunc(Create_Dtime) < To_Date('2012-sep-19','yyyy-mon-dd')
Group By Trunc(Create_Dtime)
Order By 1 Asc

Upvotes: 1

Related Questions