CODEWITHSUNDEEP
sqloracle-database
Perry
Perry

Reputation: 1337

How can I combine these 2 Oracle Queries from 1 table

I have one Oracle table that contains the daily counts for several activities. I need to create a query that shows the sum of the daily activity for a month and the same month only for the previous year. So I would end up with 6 columns something like this:

year|month|SumOfCount|PreviousYear|PreviousMonth|PreSumOfCount

I have created these 2 SQL queries this one provides the information on the date parameters entered:

select extract(year from activitydate) as yr, extract(MONTH from 
activitydate) as mon, Sum(dicalls)
from updstats
Where ACTIVITYDATE BETWEEN TO_DATE (:STARTDATE,'MM/DD/YYYY') AND 
TO_DATE(:ENDDATE,'MM/DD/YYYY')
GROUP BY extract(year from activitydate), extract(MONTH from activitydate)
order by yr, mon

and this one provides the data for the previous date range:

select extract(year from activitydate) as Pyr, extract(MONTH from 
activitydate) as Pmon, Sum(dicalls)
from updstats
Where ACTIVITYDATE BETWEEN add_months(TO_DATE (:STARTDATE,'MM/DD/YYYY'),-12) 
AND add_months(TO_DATE(:ENDDATE,'MM/DD/YYYY'),-12)
GROUP BY extract(year from activitydate), extract(MONTH from activitydate)
order by Pyr, Pmon

I am stuck and cannot figure out how I could combine these 2 to give me the data set I need.

Upvotes: 1

Views: 55

Answers (4)

Rams
Rams

Reputation: 2159

You can join those two queries as subqueries with rownumber .

 select * from 
            (select extract(year from activitydate) as yr, 
            extract(MONTH from activitydate) as mon, 
            Sum(dicalls),
           row_number() over(partition by extract(year from activitydate),extract(MONTH from activitydate) order by extract(year from activitydate),extract(MONTH from activitydate)) rn1
            from updstats Where ACTIVITYDATE BETWEEN TO_DATE (:STARTDATE,'MM/DD/YYYY') AND TO_DATE(:ENDDATE,'MM/DD/YYYY')
            GROUP BY extract(year from activitydate), extract(MONTH from activitydate)
            order by yr, mon) as n 
       join (select extract(year from activitydate) as Pyr, 
           extract(MONTH from activitydate) as Pmon,             
           Sum(dicalls),
           row_number() over(partition by extract(year from activitydate) ,extract(MONTH from activitydate) order by extract(year from activitydate) ,extract(MONTH from activitydate) as rn2
            from updstats  Where ACTIVITYDATE BETWEEN add_months(TO_DATE (:STARTDATE,'MM/DD/YYYY'),-12) 
            AND add_months(TO_DATE(:ENDDATE,'MM/DD/YYYY'),-12)
            GROUP BY extract(year from activitydate), extract(MONTH from activitydate)
            order by Pyr, Pmon)) as p on (n.rn1=p.rn2) order by n.yr,n.mon

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Get the sum of dicalls per month and year and use lag to get the value from the same month in the previous year. Use a where clause to filter for date ranges in the outer query. (This query outputs all the months and years in the table.)

select yr,mth,dicalls
,lag(yr) over(partition by mth order by yr) prev_yr
,lag(mth) over(partition by mth order by yr) prev_mth
,lag(dicalls) over(partition by mth order by yr) prev_dicalls
from (select sum(dicalls) as dicalls,extract(month from activitydate) as mth,extract(year from activitydate) as yr
      from updstats
      group by extract(month from activitydate),extract(year from activitydate)
     ) x
order by 1,2

Upvotes: 1

Mark Giaconia
Mark Giaconia

Reputation: 3953

And just because no one has answered via a CTE, here you go

with tbl_A as (
select extract(year from activitydate) as yr, extract(MONTH from 
activitydate) as mon, Sum(dicalls)
from updstats
Where ACTIVITYDATE BETWEEN TO_DATE (:STARTDATE,'MM/DD/YYYY') AND 
TO_DATE(:ENDDATE,'MM/DD/YYYY')
GROUP BY extract(year from activitydate), extract(MONTH from activitydate)
order by yr, mon
),
tbl_B as(

select extract(year from activitydate) as Pyr, extract(MONTH from 
activitydate) as Pmon, Sum(dicalls)
from updstats
Where ACTIVITYDATE BETWEEN add_months(TO_DATE (:STARTDATE,'MM/DD/YYYY'),-12) 
AND add_months(TO_DATE(:ENDDATE,'MM/DD/YYYY'),-12)
GROUP BY extract(year from activitydate), extract(MONTH from activitydate)
order by Pyr, Pmon
)
select * from tbl_A a inner join tbl_B b on a.yr = b.yr and a.mon = b.mon;

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5926

I would join the result of the two tables on month and year

select  *
from    (
            select  extract(year from activitydate) as yr,
                    extract(month from activitydate) as mon,
                    Sum(dicalls)
            from    updstats
            where   activitydate between to_date(:STARTDATE, 'MM/DD/YYYY') and
                    to_date(:ENDDATE, 'MM/DD/YYYY')
            group by  extract(year from activitydate),
                      extract(month from activitydate)
        ) ty
join    (
            select  extract(year from activitydate) as Pyr,
                    extract(month from activitydate) as Pmon,
                    Sum(dicalls)
            from    updstats
            where   activitydate between add_months(to_date(:STARTDATE, 'MM/DD/YYYY'), -12)
                    and add_months(to_date(:ENDDATE, 'MM/DD/YYYY'), -12)
            group by  extract(year from activitydate), 
                      extract(month from activitydate)
        ) ly
on      ty.yr = ly.Pyr + 1 and
        ty.mon = ly.Pmon
order by  ty.yr, ty.mon

The month will be the same, so you only have to make each year match with the previous one

Upvotes: 0

Related Questions