?
Solved

Cannot get correlated subquery to work - please advise

Posted on 2012-09-21
7
Medium Priority
?
611 Views
Last Modified: 2012-09-21
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
0
Comment
Question by:britpopfan74
  • 3
  • 3
7 Comments
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 400 total points
ID: 38423285
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
 
LVL 29

Expert Comment

by:Bill Bach
ID: 38423289
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
 

Author Comment

by:britpopfan74
ID: 38423352
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:Bill Bach
ID: 38423358
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
 

Author Comment

by:britpopfan74
ID: 38423387
I re-wrote the code like that, Bill but it's not showing me the TOTAL_DAYS
0
 
LVL 29

Accepted Solution

by:
Bill Bach earned 1600 total points
ID: 38423408
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
 

Author Closing Comment

by:britpopfan74
ID: 38423444
and adding to end ") as TOTAL_DAYS "

Thank you!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question