-1

Table1

MONTH   ACCT_NUM   PRODUCT   REVENUE
Jan     9994523    VEG        50.00 
Jan     9994523    HOT DOG    40.00 
Jan     9993644    HOT DOG    35.00 
Jan     9993644    FRIES      2.00 
Jan     9996984    VEG        60.00 
Jan     9996427               15.00

Table2

MONTH   ACCT_NUM   PRODUCT   REVENUE
Feb     9994523    VEG        50.00 
Feb     9994523    HOT DOG    40.00 
Feb     9993644    HOT DOG    35.00 
Feb     9993644    FRIES      2.00 
Feb     9996984    VEG        60.00 
Feb     9996427               15.00

Expected Output

ACCT_NUM    REVENUE_TOTAL
9993644     74.00 
9994523     180.00 
9996427     30.00 
9996984     120.00

I am trying to perform a full join on both the tables and performing a group by operation on top it. Is there anything wrong in my approach or is there a better approach the below queries

Q1 = SELECT T1.*
FROM Table1 T1
FULL OUTER JOIN Table2 T2 ON T1.ACCT_NUM = T2.ACCT_NUM

select distinct ACCT_NUM, sum(REVENUE) as REVENUE_TOTAL from Q1 group by ACCT_NUM
Shadow
  • 33,525
  • 10
  • 51
  • 64
vinay karagod
  • 256
  • 1
  • 3
  • 18
  • Does your query produces the expected outcome? If yes, does it produce the results within a reasonable timeframes? Btw mysql does not support full outer joins, so I removed that tag. Pls only use the relevant product tags, not just any random ones! – Shadow Jul 20 '17 at 23:59
  • @Shadow I don't have an environment to check it. If I had I don't need to post the question here in the first place. – vinay karagod Jul 21 '17 at 00:14
  • Then next time pls disclose such information in the question and specify a single target product environment? – Shadow Jul 21 '17 at 00:41

1 Answers1

1

Use Union All. http://sqlfiddle.com/#!6/f8e69/8

Select 
  ACCT_NUM, sum(REVENUE) REVENUE_TOTAL
From
  (
    Select ACCT_NUM, REVENUE from Table1
    Union All
    Select ACCT_NUM, REVENUE from Table2
  ) A
Group By A.ACCT_NUM

More readings for you :) Reference: What is the difference between JOIN and UNION?

UNION puts lines from queries after each other, while JOIN makes a cartesian product and subsets it...

Leo.W
  • 539
  • 1
  • 7
  • 18