Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2015
    Posts
    1

    Question Unanswered: Different SUM cases for UNION tables

    Hi - I'm a new user of DB2 and I would like to ask for help for tables using UNION ALL

    Here's the table I am using

    Table 1 - PY
    Table 2 - PM
    Table 3 - CM

    The columns I need in the tables are the account and revenue

    Here's what I want the result to look like:

    PY+PM CM PY+PM+CM
    Account 10 2 12

    I would really appreciate your help.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select 
      t.account
    , coalesce(py.revenue, 0) + coalesce(pm.revenue, 0)
    , coalesce(py.revenue, 0) + coalesce(pm.revenue, 0) + coalesce(cm.revenue, 0) 
    from (
    select account from py
      union
    select account from pm
      union
    select account from cm
    ) t
    left join py on t.account=py.account
    left join pm on t.account=pm.account
    left join cm on t.account=cm.account
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •