Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Sub Query

Posted on 2013-06-21
5
Medium Priority
?
340 Views
Last Modified: 2013-06-21
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
Comment
Question by:BananaFury
  • 3
5 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 800 total points
ID: 39265521
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
 

Author Closing Comment

by:BananaFury
ID: 39265656
Perfect, thanks so much!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39265682
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39265686
really....

there are syntax errors in both your existing query and scenario 1 ???
oh well - too late it seems.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39265707
@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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

876 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