asked on

Cannot get correlated subquery to work - please advise

Hi experts,

I am trying to write a correlated subquery within some code that gives the total claims per year while also providing the sum of total claims per month.

However, I keep getting the error "Incorrect syntax near the keyword 'AS'."

Not sure what I am doing incorrectly - please advise; thank you!

--DROP TABLE #SUMMED_CLAIMS
--SELECT * FROM #SUMMED_CLAIMS

SELECT
SUM(JAN_DAYS) AS JAN_DAYS
, SUM(FEB_DAYS) AS FEB_DAYS
, SUM(MAR_DAYS) AS MAR_DAYS
, SUM(APR_DAYS) AS APR_DAYS
, SUM(MAY_DAYS) AS MAY_DAYS
, SUM(JUN_DAYS) AS JUN_DAYS
, SUM(JUL_DAYS) AS JUL_DAYS
, SUM(AUG_DAYS) AS AUG_DAYS
, SUM(SEP_DAYS) AS SEP_DAYS
, SUM(OCT_DAYS) AS OCT_DAYS
, SUM(NOV_DAYS) AS NOV_DAYS
, SUM(DEC_DAYS) AS DEC_DAYS
--INTO #SUMMED_CLAIMS
(
SELECT
(JAN_DAYS + FEB_DAYS + MAR_DAYS + APR_DAYS + MAY_DAYS
+ JUN_DAYS + JUL_DAYS + AUG_DAYS + SEP_DAYS + OCT_DAYS
+ NOV_DAYS + DEC_DAYS) AS JAN_DAYS
, FEB_DAYS , MAR_DAYS , APR_DAYS
, MAY_DAYS , JUN_DAYS , JUL_DAYS
, AUG_DAYS , SEP_DAYS , OCT_DAYS , NOV_DAYS , DEC_DAYS
FROM #UPDATED_CLAIMS
) AS C

SOLUTION

Simone B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Bach

Your subquery doesn't seem to make sense:

SELECT (JAN_DAYS + FEB_DAYS + MAR_DAYS + APR_DAYS + MAY_DAYS
+ JUN_DAYS + JUL_DAYS + AUG_DAYS + SEP_DAYS + OCT_DAYS
+ NOV_DAYS + DEC_DAYS) AS JAN_DAYS
, FEB_DAYS , MAR_DAYS , APR_DAYS
, MAY_DAYS , JUN_DAYS , JUL_DAYS
, AUG_DAYS , SEP_DAYS , OCT_DAYS , NOV_DAYS , DEC_DAYS
FROM #UPDATED_CLAIMS

This appears to take all of the fields, add them together, and call them JAN_DAYS? I'm not sure this is what you are wanting to do, or even if you can rename the sum of a set of fields to the same name as an existing field. My guess is that the latter is your primary issue (in other words, change "AS JAN_DAYS" to "AS YEAR_DAYS") and it should work. More importantly, you should look back at your query and see if this is even what you want to do.

britpopfan74

ASKER

Bill -- very good point...I just realized when using Buttercup's coding that all days were rolling into JAN_DAYS, which is not what I want.

I want to have this be TOTAL_DAYS and preserve the sum of the months separately.

But if I put in TOTAL_DAYS after "DEC_DAYS)", I don't get any output for TOTAL_DAYS

JAN_DAYS FEB_DAYS MAR_DAYS APR_DAYS MAY_DAYS JUN_DAYS JUL_DAYS AUG_DAYS SEP_DAYS OCT_DAYS NOV_DAYS DEC_DAYS 1138 1008 1260 989 1093 1189 1123 1163 1118 983 934 1138

Bill Bach

This should be the inner query, then:
SELECT (JAN_DAYS + FEB_DAYS + MAR_DAYS + APR_DAYS + MAY_DAYS
+ JUN_DAYS + JUL_DAYS + AUG_DAYS + SEP_DAYS + OCT_DAYS
+ NOV_DAYS + DEC_DAYS) AS TOTAL_DAYS, JAN_DAYS
, FEB_DAYS , MAR_DAYS , APR_DAYS
, MAY_DAYS , JUN_DAYS , JUL_DAYS
, AUG_DAYS , SEP_DAYS , OCT_DAYS , NOV_DAYS , DEC_DAYS
FROM #UPDATED_CLAIMS

britpopfan74

ASKER

I re-wrote the code like that, Bill but it's not showing me the TOTAL_DAYS

ASKER CERTIFIED SOLUTION

Bill Bach

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

britpopfan74

ASKER

and adding to end ") as TOTAL_DAYS "

Thank you!