• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

SQL Sub Query

Hi,

Easy one for you lot I am sure..

I am super inexperienced with this sql, and as a result, for this sort of query I would create a separate tmp table at the start, and count that tmp table when calculating results. I know that is an inefficient way of doing it so would like to utilize the sub query. Could somebody please correct the following for me?

SELECT
SUM(OutstandingReserves)      AS      [OutstandingReserves],
SUM(Payments)            AS      [Payments],
SUM(TotalIncurred)            AS      [TotalIncurred],
SUM(NotificationsMTD)      AS      [NotificationsMTD],

COUNT (ClaimNumber) WHERE
(SELECT ClaimNumber WHERE Status IN ('open','reopened')
FROM mi.Archive_tblFinanceSummary_2013_03_01))
                                                   AS [CountOpenClaims]


FROM mi.Archive_tblFinanceSummary_2013_03_01


Thanks!
0
BananaFury
Asked:
BananaFury
  • 3
1 Solution
 
Surendra NathTechnology LeadCommented:
As you have not said what you are trying to achieve here ( what that sql meant to do).

I guess you are after either one of the below code

scenario 1:

SELECT 
SUM(OutstandingReserves)      AS      [OutstandingReserves],
SUM(Payments)            AS      [Payments],
SUM(TotalIncurred)            AS      [TotalIncurred],
SUM(NotificationsMTD)      AS      [NotificationsMTD],

COUNT (ClaimNumber) As CountOpenClaims
FROM mi.Archive_tblFinanceSummary_2013_03_01 T
WHERE T.ClaimNumber IN (SELECT ClaimNumber WHERE Status IN ('open','reopened')
FROM mi.Archive_tblFinanceSummary_2013_03_01)) 

Open in new window


scenario 2:

SELECT 
SUM(OutstandingReserves)      AS      [OutstandingReserves],
SUM(Payments)            AS      [Payments],
SUM(TotalIncurred)            AS      [TotalIncurred],
SUM(NotificationsMTD)      AS      [NotificationsMTD],

COUNT (CASE WHEN ClaimNumber IN ('open','reopened') THEN 1 ELSE 0 END ) AS [CountOpenClaims]
FROM mi.Archive_tblFinanceSummary_2013_03_01

Open in new window

0
 
BananaFuryAuthor Commented:
Perfect, thanks so much!
0
 
PortletPaulfreelancerCommented:
In looking at your existing query I would interpret the intention the following way
- a "scenario 1.5" ;)  it does not appear to require a subquery
SELECT
      SUM(OutstandingReserves) AS [OutstandingReserves]
    , SUM(Payments)            AS [Payments]
    , SUM(TotalIncurred)       AS [TotalIncurred]
    , SUM(NotificationsMTD)    AS [NotificationsMTD]
    , COUNT(ClaimNumber)       AS [CountOpenClaims]
FROM mi.Archive_tblFinanceSummary_2013_03_01
WHERE STATUS IN ( 'open', 'reopened' )

Open in new window

Scenario 1.5  would sum() data for all information in that table with status  'open', 'reopened' and the count() would be of only those too.

Neo_jarvis has proposed (in scenario 2) a different method which could produce a different result, the sum() results would be of all data, but the count would be of only those with status  'open', 'reopened' due to the case expression.

with respect to this:
>>I would create a separate tmp table at the start, and count that tmp table when calculating results.
If you have the confidence to build a temp table and then query from that;
it is really just a small jump to avoid the temp table and query without one

Whilst in SQL there are very few absolutes - it is likely that always building a temp table isn't the most efficient way. (i.e. It takes time/effort to produce that temp table which could be devoted to directly producing the wanted result.)

is [Archive_tblFinanceSummary_2013_03_01] such a table? (it does not appear to be a temp table - but a permanent archive)

btw:  in your existing code and scenario 1 above,  
place the from before the where in the subquery
0
 
PortletPaulfreelancerCommented:
really....

there are syntax errors in both your existing query and scenario 1 ???
oh well - too late it seems.
0
 
PortletPaulfreelancerCommented:
@Neo_jarvis: I hasten to add: "uncharacteristically"
 - no offence intended to any participant,
just watch out for the from which happens after the where
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now