?
Solved

I want to do something like what is on the 4th l line of query

Posted on 2007-08-06
1
Medium Priority
?
146 Views
Last Modified: 2010-03-19
SELECT  max(D.delta) delta_max,
            min(D.delta) delta_low,
            round(avg(D.delta), 4) delta_avg,
            count(D.delta > .5),
            count(*) total
from(
      SELECT C.LPMIMonthlyAmt CM, B.LPMIMonthlyAmt CM2, round((B.LPMIMonthlyAmt- C.LPMIMonthlyAmt),4) as delta
      from cm.dbo.martloaninterfacedetail B join
      cm2.dbo.Account_Loan C
      on        B.LOANACCTNUM = C.AccountNumber
      where B.loanacctnum IN (SELECT A.acct
                                          from dbo.cm_loans A
                                          where A.acct IN (select acct
                                                                  from CMvCM2LoanDetailCompare
                                                                  where score & 131072 = 131072)
                                          and A.live ='1')
)as D
0
Comment
Question by:donnatronious
1 Comment
 
LVL 15

Accepted Solution

by:
dbbishop earned 1000 total points
ID: 19641948
SUM(CASE D.delta WHEN > .5 THEN 1 ELSE 0 END)

You are summing 1s and 0s
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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