asked on

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!

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BananaFury

ASKER

Perfect, thanks so much!

PortletPaul

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

PortletPaul

really....

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

PortletPaul

@Neo_jarvis: I hasten to add: "uncharacteristically"
- no offence intended to any participant,
just watch out for the from which happens after the where