selecting sum items from 2 tables

regarding this topic
http://www.experts-exchange.com/questions/28716079/sum-over-partition-error.html
which paul solve it perfectly
that query selecting sum items for 2 periods for one table

but now i would like to ask
if i want to compare the data from 2 diferent tables , insted of one table
, i mean  in that query , instead of :
 FROM alpha_tran
      WHERE period = to_date('2014-09-30', 'yyyy-mm-dd')
            OR period = to_date('2013-09-30', 'yyyy-mm-dd')

Open in new window

i want to use
 FROM alpha_tran a
      WHERE a.period = to_date('2014-09-30', 'yyyy-mm-dd')
the other table "beta_tran" will have same columns : cost_center,acc_no,debit,credit
but the only difference here is this new table : "beta_tran" dont has date filed "period" in the first table
instead , it has year , month to identify the period
the join between those tables wil be on :
acc_no , cost_center and we should select from the beta_tran condition of year & month which should be equal to
to_date('2013-09-30', 'yyyy-mm-dd')
any suggession about how it may work
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Trying to accurately SUM() transactions involving joins can be very difficult, it is probably advisable to do the aggregations on "alpha_tran" then do aggregations on "beta_tran" and then join those results.

It is ALWAYS useful to have "sample data" and "expected results"

please supply some data for "alpha_tran" and some for for "beta_tran", and the expected result from that data
NiceMan331Author Commented:
Ok I will do , but actually it is sane last scenario exactly , the only different is instead of 2 periods from one table , is 2 periods from 2 tables ,
PortletPaulEE Topic AdvisorCommented:
it may be a similar scenario, but joins make it harder

imgine this:
alpha_tran has references to cost center ABC
beta_tran does NOT have cost center ABC, but does have cost center DEF that is not in alpha_tran

You need to think through the possible issues that using 2 tables introduces.

Another possibility is to UNION ALL the data from the 2 tables (using relevant where clauses) and then aggregate from that base.
   BUT it might not be appropriate here, I just don't know until I see data

nb:
I know nothing about your data or tables - seriously, nothing. I may have answered your previous question but I don't (and cannot) memorize all the facts I deal with at EE.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NiceMan331Author Commented:
Ok , give me a chance to prepare data for both tables , thanx
NiceMan331Author Commented:
here is a sample data
note :
sheet name : date , contain data from the 2 tables as per their avalable fields
and the result will be in the result sheet
Sample_1.xlsx
PortletPaulEE Topic AdvisorCommented:
OK, thanks for the sample data, it really does help.

Given the overall complexity of the previous query, I think the easiest way to introduce a second source data table is to use a UNION ALL (and convert the YEAR / MONTH columns of beta_tran into a date column called PERIOD)

So, in the existing query, inside the CTE there are these lines:
      FROM alpha_tran --<< replace THIS
      WHERE period = to_date('2014-03-31', 'yyyy-mm-dd') --<< replace THIS
            OR period = to_date('2015-03-31', 'yyyy-mm-dd') --<< replace THIS
      GROUP BY ROLLUP (COST_CENTER, ACC_NO)

Open in new window

That FROM Clause & WHERE clause get replaced like this:
      FROM (
            select COST_CENTER, ACC_NO, DEBIT, CREDIT, PERIOD
            FROM alpha_tran
            WHERE period = to_date('2014-03-31', 'yyyy-mm-dd')
                  OR period = to_date('2015-03-31', 'yyyy-mm-dd')
            UNION ALL
            select COST_CENTER, ACC_NO, DEBIT, CREDIT, add_months(trunc(to_date("YEAR",'yyyy'),'yyyy'),"MONTH")-1
            FROM beta_tran
            WHERE ( "YEAR" = extract(year from to_date('2014-03-31', 'yyyy-mm-dd'))
                   and "MONTH" = extract(month from to_date('2014-03-31', 'yyyy-mm-dd'))
                  )
                  OR ( "YEAR" = extract(year from to_date('2015-03-31', 'yyyy-mm-dd'))
                   and "MONTH" = extract(month from to_date('2015-03-31', 'yyyy-mm-dd'))
                     )
           )
      GROUP BY ROLLUP (COST_CENTER, ACC_NO)

Open in new window

The overall query loos like this:
WITH CC_ACCNO
AS (
      SELECT
            1 QUERY_NUM
          , GROUPING_ID(COST_CENTER, ACC_NO) GROUPING_LEVEL

          , COST_CENTER
          , ACC_NO

            /* columns for "current period" calculations */
          , SUM(CASE
                  WHEN period = to_date('2015-03-31', 'yyyy-mm-dd') AND acc_no = 41010 THEN (credit - debit)
                  ELSE 0
            END) cur_41010
          , SUM(CASE
                  WHEN period = to_date('2015-03-31', 'yyyy-mm-dd') AND acc_no <> 41010 OR acc_no IS NULL THEN 0
                  ELSE (credit - debit)
            END) cur_not_41010

            /* columns for "previous period" calculations */
          , SUM(CASE
                  WHEN period = to_date('2014-03-31', 'yyyy-mm-dd') AND acc_no = 41010 THEN (credit - debit)
                  ELSE 0
            END) prv_41010
          , SUM(CASE
                  WHEN period = to_date('2014-03-31', 'yyyy-mm-dd') AND acc_no <> 41010 OR  acc_no IS NULL THEN 0
                  ELSE (credit - debit)
            END) prv_not_41010
      FROM (
            select COST_CENTER, ACC_NO, DEBIT, CREDIT, PERIOD
            FROM alpha_tran
            WHERE period = to_date('2014-03-31', 'yyyy-mm-dd')
                  OR period = to_date('2015-03-31', 'yyyy-mm-dd')
            UNION ALL
            select COST_CENTER, ACC_NO, DEBIT, CREDIT, add_months(trunc(to_date("YEAR",'yyyy'),'yyyy'),"MONTH")-1
            FROM beta_tran
            WHERE ( "YEAR" = extract(year from to_date('2014-03-31', 'yyyy-mm-dd'))
                   and "MONTH" = extract(month from to_date('2014-03-31', 'yyyy-mm-dd'))
                  )
                  OR ( "YEAR" = extract(year from to_date('2015-03-31', 'yyyy-mm-dd'))
                   and "MONTH" = extract(month from to_date('2015-03-31', 'yyyy-mm-dd'))
                     )
           )
      GROUP BY ROLLUP (COST_CENTER, ACC_NO)
  )
, ACCNO
AS (
      SELECT
            2 AS QUERY_NUM
          , 0 AS GROUPING_LEVEL
          , 3100 AS Cost_Center
          , ACC_NO
          , SUM(CUR_41010) AS CUR_41010
          , SUM(CUR_NOT_41010) AS CUR_NOT_41010
          , SUM(PRV_41010) AS PRV_41010
          , SUM(PRV_NOT_41010) AS PRV_NOT_41010
      FROM CC_ACCNO
      WHERE GROUPING_LEVEL = 0
      GROUP BY ACC_NO
)
SELECT
      *
FROM CC_ACCNO
WHERE GROUPING_LEVEL < 3
UNION ALL
      SELECT
            *
      FROM ACCNO
UNION ALL
      SELECT
            *
      FROM CC_ACCNO
      WHERE GROUPING_LEVEL = 3

Open in new window

also see: http://sqlfiddle.com/#!4/ffdae/1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
good method
it is clear & understood really wonderfull
let me try then be back
let me ask a qustion :
what "with" does ?
i didn't get how it works
1- is < "WITH CC_ACCNO" creating something like a view , then you select from ?
2- ", ACCNO" is necessary to select from cc_accno , or , i can select from another table ?
i mean , for example , can i select cc_accno from table A , and accno from table B
then select from both accno , cc_accno ?
3-regarding group by rollup function , i noticed that the grouping level started with 0
and summary per cost_center giving 1 , which is logic for me , but why the grand total giving grouping level of 3 , not 2 ?
4-lastly , and sorry for those questions :
SELECT
      *
FROM CC_ACCNO
WHERE GROUPING_LEVEL < 3
UNION ALL
      SELECT
            *
      FROM ACCNO
UNION ALL
      SELECT
            *
      FROM CC_ACCNO
      WHERE GROUPING_LEVEL = 3

Open in new window

why we have to union all the last one where grouping level = 3
why not the first one be only : SELECT
      *
FROM CC_ACCNO
PortletPaulEE Topic AdvisorCommented:
>>"what "with" does ?"
Oracle uses the term "subquery factoring" for this and I suggest you could read: http://dba-oracle.com/t_oracle_subquery_factoring.htm and there are more lnks from that page.

A much more broadly used term (not Oracle specific) is "common table expression" or simply "CTE". I prefer to use this term/abbreviation.

The BIG BIG BIG BIG advantage we get by using "with" is that we can RE-USE the result

So, we get the subquery I previously called (a) and we can reuse it to create (b) and then we can also re-use parts of (a) for all the cross-row calculations and so, that is why I use "with".
PortletPaulEE Topic AdvisorCommented:
>>"why we have to union all the last one where grouping level = 3
why not the first one be only : SELECT
      *
FROM CC_ACCNO
"

try it

Then you can tell me why it is necessary.
NiceMan331Author Commented:
YES PAUL
it is ok
only i still don't understand why the grouping level becomes 3

lastly ,
i'm really thanking you , you make the query very easy for me , instead the difficulty
but , for me as a beginner , before , i didn't know about those method ( "with" and "group by rollup")
and may be a have some other works which i built it hard , while may be there is an easier future , in here , what is your personnel advice for me about where i can learn & practice more about sql , yes the needs leads human to search and ask to solve his problem , but it is good also to learn before you have a problem

i have no problem about the fees , just i want to learn & practice with examples
oracle documentation may be the best source , but i have lack of English , and it is difficult to read & understood , if any source with learning by examples , it will be better for me
thanx a lot
PortletPaulEE Topic AdvisorCommented:
NiceMan331 I would recommend BOOKS and many now come with learning aids (on a CD) and you may be able to find some in your native language too.

However I do not know which books to recommend because I learned so many years ago they would not be available now even if  could remember who the authors were.

If you were using SQL Server I would recommend books by Itzik Ben-Gan but I don't k now who to recommend for Oracle. You could ask a new question about learning advice.

----------



>>"only i still don't understand why the grouping level becomes 3 "
each "level of summary" gets a new number

but that is also a new question, because you previously asked this:
>>"why we have to union all the last one where grouping level = 3
why not the first one be only : SELECT
      *
FROM CC_ACCNO
"

Please go back to this comment: http://www.experts-exchange.com/questions/28716079/sum-over-partition-error.html#a40990125

Those blue rows as summary lines, the upper 2 blue rows are "sub totals", the bottom blue row is a "grand total"

so, three different "levels" of data are in that report
1 = not summarized
2 = sub totals >> produced by "group by rollup"
3 = grand total >> produced by "group by rollup"

BUT BUT BUT

Your requirement has TWO QUERIES (a) and (b)

You need to insert (b) just above the grand totals

NOW do you understand why we use UNION ALL?
NiceMan331Author Commented:
sorry again for disconnection
actually i got an answer , your query is so cool , i will not go ahead to debate you regarding the above , i just want to understand it
so, three different "levels" of data are in that report
1 = not summarized
2 = sub totals >> produced by "group by rollup"
3 = grand total >> produced by "group by rollup"

1 = got grouping level  = 0
2= got grouping level = 1
3 = (which by logic should got 2 , it got grouping level = 3 )
PortletPaulEE Topic AdvisorCommented:
I was not being literal... perhaps I should have used

so, three different "levels" of data are in that report
i = not summarized
ii = sub totals >> produced by "group by rollup"
iii = grand total >> produced by "group by rollup"

I wan't attempt to explain the output of that internal Oracle function, please refer to their documentation, e.g. http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions063.htm
NiceMan331Author Commented:
good
now i found my missed grouping_level "2"
when using group by cube , it will return
0 for non_summary
1 summary per cost_center
2 summary per account
3 summary all
and return all correct summ for all what i need from 1st requirement ( with no need for using the ACCNO )
the only thing different now is : the grouping level # 2 returned  with null value for cost_center , i will try to look for a solution for it , because this way is really shortcut the bigger sql statement ( according to the many factors which must be repeated 3 times in the first query )
NiceMan331Author Commented:
the query becomes like this

WITH CC_ACCNO
AS (
 SELECT
            1 QUERY_NUM
          , GROUPING_ID(COST_CENTER, ACC_NO) GROUPING_LEVEL

          ,COST_CENTER
          ,acc_no

          , SUM(CASE
                  WHEN period = to_date('2015-09-30', 'yyyy-mm-dd') AND entry_type is null and 
                  acc_no = 41010 THEN (credit - debit)
                  ELSE 0
            END) cur_sls
          , SUM(CASE
                  WHEN period = to_date('2015-09-30', 'yyyy-mm-dd') AND entry_type is null AND 
                  acc_no <> 41010  THEN 
                  (DEBIT - credit )
            END) cur_exp,
          

           SUM(CASE
                  WHEN period = to_date('2014-09-30', 'yyyy-mm-dd') AND acc_no = 41010 THEN (credit - debit)
                  ELSE 0
            END) prv_sls
          , SUM(CASE
                  WHEN period = to_date('2014-09-30', 'yyyy-mm-dd') AND acc_no <> 41010  THEN 
                  (DEBIT - credit )
            END) prv_exp,
                        SUM(CASE
                  WHEN period = to_date('2015-09-30', 'yyyy-mm-dd') and entry_type = 2
                  and acc_no <> 41010 
                  THEN (debit - credit) * -1
                   
                  ELSE 0
            END) cur_pastry,
            SUM(CASE
                  WHEN period = to_date('2015-09-30', 'yyyy-mm-dd') and entry_type = 4
                  and acc_No <> 41010 
                  THEN (debit - credit) 
                  ELSE 0
            END) cur_ind,
            SUM(CASE
                  WHEN period = to_date('2015-09-30', 'yyyy-mm-dd') --and entry_type = 11
                  and acc_No <> 41010 
                  THEN (debit - credit) 
                  ELSE 0
            END) 
             cur_not_pst_ind,
            
            SUM(CASE
                  WHEN period = to_date('2014-09-30', 'yyyy-mm-dd') and entry_type = 2
                  and acc_no <> 41010 
                  THEN (debit - credit) 
                  ELSE 0
            END) prv_pastry,
          SUM(CASE
                  WHEN period = to_date('2014-09-30', 'yyyy-mm-dd') and entry_type = 4
                  and acc_no <> 41010 
                  THEN (debit - credit) 
                  ELSE 0
            END) prv_ind,
          SUM(CASE
                  WHEN period = to_date('2014-09-30', 'yyyy-mm-dd') and (entry_type is null or entry_type not in (2,4))
                  and acc_no <> 41010 and cost_center <> 3198 
                  
                  THEN (debit - credit) 
                  ELSE 0
            END) prv_not_pst_ind
          
      FROM (
      select COST_CENTER, ACC_NO, DEBIT, CREDIT, PERIOD,entry_type
            FROM alpha_tran
            WHERE period = to_date('2014-09-30', 'yyyy-mm-dd')
                
            UNION ALL

            SELECT   to_number(a.cost_center) ,to_number(c.code_trans) ,(a.c_op_deb + a.c_tr_deb),(a.c_op_cr + a.c_tr_cr)
            , add_months(trunc(to_date(a.year,'yyyy'),'yyyy'),a.period_no)-1 , null
            from H2002.COST_BAL a,h2002.acc_master_file c 
            where a.acc_no = c.acc_no and c.a_type = 2
       and (A.YEAR = extract(year from to_date('2015-09-30', 'yyyy-mm-dd')))
                   and (A.PERIOD_NO = extract(month from to_date('2015-09-30', 'yyyy-mm-dd')))
      and a.acc_no between '40000' and '49000'
 and ((a.cost_center between '3101' and '3198') or (a.cost_center in('3010','3030')))
                  )
                 
                     
           
      GROUP BY cube (COST_CENTER, ACC_NO))
      SELECT
      QUERY_NUM
    , GROUPING_LEVEL
    , COST_CENTER
    , ACC_NO
    , CUR_sls
    , CUR_exp
     , (case when grouping_level < 2 then CUR_exp / NULLIF( (select CUR_sls 
                                 from CC_ACCNO f 
                                 where f.COST_CENTER = CC_ACCNO.COST_CENTER 
                                 and f.grouping_level = 1
                                 and rownum = 1 ) ,0)
        ELSE CUR_exp / NULLIF( (select CUR_sls 
                                 from CC_ACCNO f 
                                 where 
                                  f.grouping_level = 3
                                 and rownum = 1 ) ,0)
       END)  
       CUR_EXP_SLS_PER,
       
     PRV_sls
    , PRV_exp
    
,        (case when grouping_level < 2 then PRV_exp / NULLIF( (select PRV_sls 
                                 from CC_ACCNO f 
                                 where f.COST_CENTER = CC_ACCNO.COST_CENTER 
                                 and f.grouping_level = 1
                                 and rownum = 1) ,0)
        ELSE PRV_exp / NULLIF( (select PRV_sls 
                                 from CC_ACCNO f 
                                 where  f.grouping_level = 3
                                 and rownum = 1) ,0)
       END) AS 
       PRV_EXP_SLS_PER,
          cur_pastry
       ,prv_pastry,
     CASE WHEN GROUPING_LEVEL < 2 THEN
        CUR_exp / NULLIF( (select CUR_exp 
                                 from CC_ACCNO f 
                                 where f.COST_CENTER = CC_ACCNO.COST_CENTER 
                                 and f.grouping_level = 1
                                 and rownum = 1) ,0)
        ELSE 
        CUR_exp / NULLIF( (select CUR_exp 
                                 from CC_ACCNO f 
                                 where  f.grouping_level = 3
                                 and rownum = 1) ,0)
        
       END AS CUR_EXP_ALL_PER
       
    ,    CASE WHEN GROUPING_LEVEL < 2 THEN
            PRV_exp / NULLIF( (select PRV_exp 
                                 from CC_ACCNO f 
                                 where f.COST_CENTER = CC_ACCNO.COST_CENTER 
                                 and f.grouping_level = 1
                                 and rownum = 1) ,0)
        ELSE PRV_exp / NULLIF( (select PRV_exp 
                                 from CC_ACCNO f 
                                 where 
                                 f.grouping_level = 3
                                 and rownum = 1) ,0)
       END AS PRV_EXP_ALL_PER
       
        , CASE WHEN GROUPING_LEVEL in(1,3) THEN
        (cur_sls-prv_sls)/nullif(prv_sls,0)
        ELSE NULL
       END AS CUR_PRV_SLS_PER


        , CASE WHEN GROUPING_LEVEL in(1,3) THEN
        (CUR_exp-PRV_exp) 
                                  / NULLIF( PRV_exp,0) 
                                                                          
       ELSE NULL
       END AS 
       CUR_PRV_exp_PER
       
FROM CC_ACCNO

      
order by 3,4

Open in new window

PortletPaulEE Topic AdvisorCommented:
did you try group by rollup

instead of group by cube
NiceMan331Author Commented:
yes
group by rollup wil give details & 2 summary only like this
0 for non_summary
1 summary per cost_center
3 summary all

while using "cube" will gives 3 summary like this :
0 for non_summary
1 summary per cost_center
2 summary per account
3 summary all
NiceMan331Author Commented:
i make the select statement after the group by cube like this
SELECT QUERY_NUM,
         GROUPING_LEVEL,
         nvl(COST_CENTER,3100) cost_center,
         ACC_NO,
         CUR_sls,
         CUR_exp,

Open in new window


and it is ok now , it gives same result of your previous one
PortletPaulEE Topic AdvisorCommented:
OK. thanks. Good work, it appears you don't need me now (which is good).
NiceMan331Author Commented:
no teacher , you were , and still my teacher
you know , your method benifit me alot in other quiries
by the way , please take alook about this topic which still open till now
http://www.experts-exchange.com/Database/Oracle/Q_28586343.html
may be will have better method
PortletPaulEE Topic AdvisorCommented:
no, it is too late for me (23:42 as I type) and will be in bed soon
NiceMan331Author Commented:
ok , take care
tomorrow
good dreams
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.