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
britpopfan74Asked:
Who is Participating?
 
Bill BachPresidentCommented:
Ahh, you are right.  To fix this, you must ALSO add the field to the outer query, like this:

SELECT
 SUM(TOTAL_DAYS) AS TOTAL_DAYS
, 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
FROM
(
0
 
Simone BSenior E-Commerce AnalystCommented:
You need another "FROM" as below:


--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
FROM
(
    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
0
 
Bill BachPresidentCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
britpopfan74Author Commented:
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
0
 
Bill BachPresidentCommented:
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
0
 
britpopfan74Author Commented:
I re-wrote the code like that, Bill but it's not showing me the TOTAL_DAYS
0
 
britpopfan74Author Commented:
and adding to end ") as TOTAL_DAYS "

Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.