First off happy holidays to you all and I hope if anyone else is working this weekend like me everything is going smoothly. so here is this situation. I have two separate queries I am trying to merge into a sub-query however I can’t seem to get it to work properly and I know it is a simple syntax issue that someone with more skills can identify.
Here are the 2 queries:
QUERY 1
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
Which returns:
YEAR MONTH TOTAL_PLACED
2012 8 3101542.03
2012 9 285786.43
2012 10 1224473.49
2012 11 327823.32
2012 12 315477.44
QUERY 2
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
WHICH RETURNS:
YEAR MONTH TOTAL_LOCATED
2012 8 2905070.72
2012 9 202220.81
2012 10 462872.94
2012 11 77451.27
2012 12 22331.84
Both return the values expected. The problem I have is when I try to nest (sub-query) the 2nd query into the first with this query:
SELECT YEAR(araclstdte) AS YEAR,
MONTH(araclstdte) AS MONTH,
SUM(AFACINIAMT) AS TOTAL_PLACED,
(SELECT SUM(AFACINIAMT)
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW NOT like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'BAD%'
AND ARR.ARRELPHASE = 'REGULAR')AS TOTAL_LOCATED
FROM SQLUser.STFIELDAUD STF,
SQLUser.ARRELATIONSHIP ARR,
SQLUser.ARACCOUNT ARA,
SQLUser.AFACCOUNT AFA
WHERE STF.STAUDKEY=ARR.ARRELID
AND ARA.ARACID=ARR.ARRELACID
AND AFA.AFACKEY=ARR.ARRELAFID
AND ARR.ARRELCLTID = 'LATX1'
AND STF.STAUDFLDNAME = 'ARRELSTATUSID'
AND STF.STAUDFLDEXTNEW like 'BAD%'
AND STF.STAUDFLDEXTOLD like 'NEWACCT'
GROUP BY YEAR(araclstdte), MONTH(araclstdte)
ORDER BY YEAR(araclstdte), MONTH(araclstdte)
I get the following which is incorrect:
YEAR MONTH TOTAL_PLACED TOTAL_LOCATED
2012 8 3101542.03 3669947.58
2012 9 285786.43 3669947.58
2012 10 1224473.49 3669947.58
2012 11 327823.32 3669947.58
2012 12 315477.44 3669947.58
Please help if you can!. Thanks and Happy Holidays
http://www.w3schools.com/sql/sql_union.asp