How to add two tables having sum of amounts on the basis of year in SQL(toad)?

Question

First Table :

SELECT A.BU,A.PROJECT,SUM(A.BUDGET_AMOUNT),SUBSTR(A.BUDGET_PERIOD,3,2)
FROM BUDGET_SUMMARY A
GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)

And the second table:

SELECT B.BU,A.PROJECT,SUM(B.ACTUAL_AMOUNT),SUBSTR(B.ACTUAL_PERIOD,3,2)
FROM ACTUAL_SUMMARY B
GROUP BY B.BU,B.PROJECT,SUBSTR(B.ACTUAL_PERIOD,3,2)

Now,I want to join the both above mentioned table into one table in the form of

BU|PROJECT|SUM(BUDGET_AMOUNT)|SUBSTR(A.BUDGET_PERIOD,3,2)|SUM(B.ACTUAL_AMOUNT)|SUBSTR(B.ACTUAL_PERIOD,3,2)

Please before you post look at the formatted version of your post below the edit box. Read the edit help re inline & block formats for code & quotations. Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] For output tables use code format. — philipxy, Jul 12 '20 at 09:24
what exactly are your intended join-columns on these 2 sub-selects? a.PROJECT=b.PROJECT? — Michael Hauptmann, Jul 12 '20 at 09:29
Sorry for not defining my question properly,will keep it in mind to post carefully next time and yes I was intending to join two tables and two column actual amount and budget amount column all should be in one table.and I got the answer thank you — Key_girl, Jul 12 '20 at 11:30
See [How do comment @replies work?](https://meta.stackexchange.com/questions/43019/how-do-comment-replies-work) to learn to use @x to notify one non-sole non-poster commenter x re a comment. Please clarify via edits, not comments. Please consider editing your post to improve it. Posets are generally here forever for others to be helped & also poorly received posts count towards posting bans, even if deleted. [ask] [help] — philipxy, Jul 12 '20 at 17:21

score 0 · Accepted Answer · answered Jul 12 '20 at 09:32

SELECT A.BU,A.PROJECT, SUM(A.BUDGET_AMOUNT), SUBSTR(A.BUDGET_PERIOD,3,2), SUM(B.ACTUAL_AMOUNT)
FROM BUDGET_SUMMARY A
GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)

Or of the two table are not for the same value you can use JOIN (inner join for amtching values)

    SELECT A.BU,A.PROJECT,SUM(A.BUDGET_AMOUNT),SUBSTR(A.BUDGET_PERIOD,3,2), T.SUM_BUDGET_AMOUNT, T.SUB_ACTUAL_PERIOD
    FROM BUDGET_SUMMARY A
    INNER JOIN  (
        SELECT B.BU,A.PROJECT,SUM(B.ACTUAL_AMOUNT) sum_BUDGET_AMOUNT,SUBSTR(B.ACTUAL_PERIOD,3,2) SUB_ACTUAL_PERIOD
        FROM ACTUAL_SUMMARY B
        GROUP BY B.BU,B.PROJECT,SUBSTR(B.ACTUAL_PERIOD,3,2)
        ) T  ON T.BU = A.BY 
            AND T.PROJECT = A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)
                AND T.SUB_ACTUAL_PERIOD = ,SUBSTR(A.BUDGET_PERIOD,3,2)
    GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)

left join if you have aprtila match

    SELECT A.BU,A.PROJECT,SUM(A.BUDGET_AMOUNT),SUBSTR(A.BUDGET_PERIOD,3,2), T.SUM_BUDGET_AMOUNT, T.SUB_ACTUAL_PERIOD
    FROM BUDGET_SUMMARY A
    LEFT  JOIN  (
        SELECT B.BU,A.PROJECT,SUM(B.ACTUAL_AMOUNT) sum_BUDGET_AMOUNT,SUBSTR(B.ACTUAL_PERIOD,3,2) SUB_ACTUAL_PERIOD
        FROM ACTUAL_SUMMARY B
        GROUP BY B.BU,B.PROJECT,SUBSTR(B.ACTUAL_PERIOD,3,2)
        ) T  ON T.BU = A.BY 
            AND T.PROJECT = A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)
                AND T.SUB_ACTUAL_PERIOD = ,SUBSTR(A.BUDGET_PERIOD,3,2)
    GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)

yes i need a partial match..left join worked thank you – Key_girl Jul 12 '20 at 11:26 — Key_girl, Jul 12 '20 at 11:26

score 0 · Answer 2 · answered Jul 12 '20 at 09:34

just guessing, your description is hard to read and not executable. named-sub-queries should help to get your desired result (assuming "project" can be used as join-column)

with budget as
(
SELECT A.BU,A.PROJECT,SUM(A.BUDGET_AMOUNT) sum_amount,SUBSTR(A.BUDGET_PERIOD,3,2) m_period FROM BUDGET_SUMMARY A GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)
), actual as
(
SELECT B.BU,B.PROJECT,SUM(B.ACTUAL_AMOUNT) sum_amount,SUBSTR(B.ACTUAL_PERIOD,3,2) m_period FROM ACTUAL_SUMMARY B GROUP BY B.BU,B.PROJECT,SUBSTR(B.ACTUAL_PERIOD,3,2)
)

select  budget.BU,budget.PROJECT,budget.sum_amount,budget.m_period,actual.sum_amount,actual.m_period
from budget
join actual on budget.project=actual.project

(possibly a left-join is needed)

Please do not guess in answer posts, it makes a mess, ask for clarification by commenting on the question & flag or close vote for lack of clarity. — philipxy, Jul 12 '20 at 09:37
Thank you Michael for you extended help,and sorry for not clearing my question,as it was my first query and was too busy with the issue,and yes left join worked ,your answer helped thank you — Key_girl, Jul 12 '20 at 11:28

How to add two tables having sum of amounts on the basis of year in SQL(toad)?

2 Answers2