SQL- Oracle Grouping results rows

Question

The query below:

Select
trunc(create_dtime) as Day,count(create_dtime) as DLs
From Player_Tapjoy
Where
Trunc(Create_Dtime) >= To_Date('2012-sep-01','yyyy-mon-dd')
And Trunc(Create_Dtime) < To_Date('2012-sep-03','yyyy-mon-dd')
Group by trunc(create_dtime)
Union All
Select trunc(create_dtime) as Day,
Count(Create_Dtime) As DLs
From Player_aux_pt
Where
Site = 'AppCircle' And
Trunc(Create_Dtime) >= To_Date('2012-sep-01','yyyy-mon-dd')
And Trunc(Create_Dtime) < To_Date('2012-sep-03','yyyy-mon-dd')
Group By Trunc(Create_Dtime)

Gives me the following result:

   Day           DLs
02-Sep-12        6920
01-Sep-12        6630
02-Sep-12        3009
01-Sep-12        3637

How can I combine these so that Each day only has one row, and it is ordered correctly?

@Roger I don't understand what you mean. My query is too long to be useful? — Americo, Sep 18 '12 at 19:10
he means he doesn't like the way you've formatted your code, it's hard to see the differentiation between the two queries. See this question for some commonly used SQL formatting standards (http://stackoverflow.com/questions/519876/sql-formatting-standards) — Nathan Koop, Sep 19 '12 at 13:06

score 2 · Accepted Answer · answered Sep 18 '12 at 21:39

WITH
PTJ AS
(
    SELECT
      TRUNC(CREATE_DTIME) AS DAY,
      COUNT(CREATE_DTIME) AS DLS
    FROM PLAYER_TAPJOY
    WHERE TRUNC(CREATE_DTIME) >= TO_DATE('2012-sep-01', 'yyyy-mon-dd')
    AND   TRUNC(CREATE_DTIME) < TO_DATE('2012-sep-03', 'yyyy-mon-dd')
    GROUP BY TRUNC(CREATE_DTIME)
),
PAP AS
(
    SELECT
      TRUNC(CREATE_DTIME) AS DAY,
      COUNT(CREATE_DTIME) AS DLS
    FROM PLAYER_AUX_PT
    WHERE SITE = 'AppCircle'
    AND   TRUNC(CREATE_DTIME) >= TO_DATE('2012-sep-01', 'yyyy-mon-dd')
    AND   TRUNC(CREATE_DTIME) < TO_DATE('2012-sep-03', 'yyyy-mon-dd')
    GROUP BY TRUNC(CREATE_DTIME)
)
SELECT
  COALESCE(PTJ.DAY, PAP.DAY) AS DAY,
  PTJ.DLS AS PTJ_DLS,
  PAP.DLS AS PAP_DLS,
  COALESCE(PTJ.DLS, 0) + COALESCE(PAP.DLS, 0) TOTAL_DLS
FROM PTJ
FULL OUTER JOIN PAP
ON PTJ.DAY = PAP.DAY
ORDER BY COALESCE(PTJ.DAY, PAP.DAY)

Americo · Answer 2 · 2012-09-18T19:11:36.687

select day,sum(DLs) as Tot_DLs
From
(
Select
 trunc(create_dtime) as Day,count(create_dtime) as DLs
  From Player_Tapjoy
 Where
 Trunc(Create_Dtime) >= To_Date('2012-sep-01','yyyy-mon-dd')
   And Trunc(Create_Dtime) < To_Date('2012-sep-03','yyyy-mon-dd')
   Group by trunc(create_dtime)
  Union All
 Select trunc(create_dtime) as Day,
  Count(Create_Dtime) As DLs
  From Player_aux_pt
    Where
  Site = 'AppCircle' And
   Trunc(Create_Dtime) >= To_Date('2012-sep-01','yyyy-mon-dd')
    And Trunc(Create_Dtime) < To_Date('2012-sep-03','yyyy-mon-dd')
   Group By Trunc(Create_Dtime)
    )
   Group By Day
   Order By Day Asc

;

SQL- Oracle Grouping results rows

2 Answers2