Add values together from two SQL queries to create a third value

Question

My first post so here goes, been doing some basic SQL but having to learn a bit more due to more responsibilities.

Query 1 below gives me a year to date value of all codes in our Financial System, these run from Objective AAAAA to ZZZZZ and Subjective 0000 to 9999, not all codes are in use, at present this is at week 28 in year 15.

Query 2 below gives me the total value of journals (in three journal tables) that have been input this week (29), they have a field called jnl_processed that can be selected with the line AND JNL_PROCESSED = 'N', this adds up the value of the no processed journals as per the relevant Objective and Subjective codes, there are are lot less codes on this table compared to table 1.

I am looking ahead to year end and a worthwhile query would be to see the value of all the Year_To_Date (YTD) in this instance week 28 added to the journal value week 29 to create and new column total.

I have the two queries separately below, but struggling to merge the two, can anybody assist?

Query 1

SELECT OBJ_FULL,SUBJ_FULL,ACT_YTD_TY 
FROM DB2ADM2.TFINCATP
WHERE OBJ_FULL = 'TBBBB'
AND ACT_YTD_TY <> 0.00

Produces this:

OBJ_FULL    SUBJ_FULL   ACT_YTD_TY
TBBBB           3420            12,000.00 
TBBBB           3473            18,453.02 
TBBBB           3524            2,480.40 
TBBBB           3704            585.00 
TBBBB           7704            -4,142.71 
TBBBB           8199            -25.00 
TBBBB           9400            -168,363.10 
TBBBB           9403            -457.56 
TBBBB           9404            -9,666.73 
TBBBB           9405            -13,098.05 

Query 2

SELECT  JNL_OBJ, JNL_SUBJ, SUM(JNL_VALUE)
FROM DB2ADM2.JNLFILE
WHERE JNL_PROCESSED = 'N' AND JNL_YEAR = '15' 
GROUP BY JNL_OBJ, JNL_SUBJ
UNION
SELECT  JNL_OBJ, JNL_SUBJ, SUM(JNL_VALUE)
FROM DB2ADM2.JNLRFILE
WHERE JNL_PROCESSED = 'N'  AND JNL_YEAR = '15'
GROUP BY JNL_OBJ, JNL_SUBJ
UNION
SELECT  JNL_OBJ, JNL_SUBJ, SUM(JNL_VALUE)
FROM DB2ADM2.JNLYFILE
WHERE JNL_PROCESSED = 'N'  AND JNL_YEAR = '15' 
GROUP BY JNL_OBJ, JNL_SUBJ

Produces this:

JNL_OBJ JNL_SUBJ    VALUE
TBBBB   9404            -547.78 
TBBBB   9405            -24.39 
TBBCA   9404            547.78 
TBBCC   9400            24.39 

Ideally the result should return (using four codes as an example)

OBJ     SUBJ    YTD           VALUE        TOTAL
TBBBB   3420    12,000.00   0.00       12,000.00 
TBBBB   9403    -457.56     0.00       -457.56 
TBBBB   9404    -9,666.73   -547.78    -10,214.51 
TBBBB   9405    -13,098.05  -24.39     -13,122.44 

Any assistance greatly appreciated, I don't know if there is a simpler way to merge the three journal tables rather than using UNION, the columns are the same apart from JNL_SOURCE which is JA, JC and JD for each of the tables.

This is the current data being returned at 15:30 on 21 Oct 15

OBJ         SUBJ     YTD        VALUE   TOTAL
TBBBB   9404    -9666.73    -547.78 -10214.51
TBBBB   9405    -13098.05   -24.39  -13122.44

There are several other codes with a value in the YTD column but the VALUE column is zero, I need to include all codes.

Answer 1

You have two perfectly good queries worked out. Now you need to use them as if they were tables -- think of them as virtual tables -- and join them together.

Something like this will do the trick.

SELECT query1.OBJ_FULL, query1.SUBJ_FULL, 
       query1.ACT_YTD_TY, query2.VALUE,
       (query1.ACT_YTD_TY + query2.VALUE) AS TOTAL 
  FROM ( 
          /* your first query */
       ) query1
  JOIN (
          /* your second query */
       ) query2 ON query1.OBJ_FULL = query2.JNL_OBJ
               AND query1.SUBJ_FULL = query2.JNL_SUBJ

That does the trick of laminating your two result sets together.

Answer 2

Try ...

SELECT EOYQuery.OBJ_FULL AS OBJ,
       EOYQuery.SUBJ_FULL AS SUBJ,
       EOYQuery.ACT_YTD_TY AS YTD,
       JournalsQuery.JournalsValue AS VALUE,
       ( EOYQuery.ACT_YTD_TY + JournalsQuery.JournalsValue ) AS TOTAL
FROM ( SELECT OBJ_FULL,
              SUBJ_FULL,
              ACT_YTD_TY
       FROM DB2ADM2.TFINCATP
       WHERE OBJ_FULL = 'TBBBB'
         AND ACT_YTD_TY <> 0.00 ) EOYQuery,
     ( SELECT JNL_OBJ,
              JNL_SUBJ,
              SUM( JNL_VALUE ) AS JournalsValue
       FROM DB2ADM2.JNLFILE
       WHERE JNL_PROCESSED = 'N' AND
             JNL_YEAR = '15' 
       GROUP BY JNL_OBJ,
                JNL_SUBJ
       UNION SELECT JNL_OBJ,
                    JNL_SUBJ,
                    SUM( JNL_VALUE ) AS JournalsValue
             FROM DB2ADM2.JNLRFILE
             WHERE JNL_PROCESSED = 'N' AND
                   JNL_YEAR = '15'
             GROUP BY JNL_OBJ,
                      JNL_SUBJ
       UNION SELECT JNL_OBJ,
                    JNL_SUBJ,
                    SUM( JNL_VALUE ) AS JournalsValue
             FROM DB2ADM2.JNLYFILE
             WHERE JNL_PROCESSED = 'N' AND
                   JNL_YEAR = '15'
                               GROUP BY JNL_OBJ,
                      JNL_SUBJ ) JournalsQuery
WHERE EOYQuery.OBJ_FULL  = JournalsQuery.JNL_OBJ
  AND EOYQuery.SUBJ_FULL = JournalsQuery.JNL_SUBJ;

If this is or is not a suitable answer, then please feel free to comment accordingly.

Please Note : Without knowing for sure the structure of the tables being referenced as well as a representative (imitation) sample of the data stored in those tables, I am unable to test my answer.

I could try to deduce these from the samples you have supplied, but constructing the script necessary to replicate these in MySQL would take more time than I currently have. If you have or can construct such script yourself and you are allowed to post it, and do so, then I will be able to test my answer and hunt down any bugs that may be present.