britpopfan74
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
ASKER
I re-wrote the code like that, Bill but it's not showing me the TOTAL_DAYS
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
and adding to end ") as TOTAL_DAYS "
Thank you!
Thank you!
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.