Sql Server - Selecting Logical Results in a Query

Suppose I have a table: 开发者_StackOverflow中文版ACCOUNT

It has columns: ID, NAME, BALANCE

I want to determine if the SUM of balances of people who has balance more than $ 2,000 is greater than $ 50,000. It means I want to select a logical value. Because I don't want to get specific sum, I only need comparison result. I want to be able to write something like:

SELECT SUM(BALANCE) > 50000 
  FROM ACCOUNT
 WHERE BALANCE > 2000

Sql Server says it is syntax error. So I have to do:

SELECT SUM(BALANCE)  
  FROM ACCOUNT
 WHERE BALANCE > 2000

Then compare the result to 50000. But taking sum of all the items after reaching 50000 is redundant since I only compare it to 50000.

So how can I write this query in a way to stop after reaching comparison value and give me the logical result?

---

You don't.

How does SQL Server know you have only positive values? If you have 10 values, and the 8th one gets you over 50,000, but 9 and 10 are both -10,000, wouldn't that be an issue?

---

This question was asked in 2011. In versions prior to 2012 there isn't really a clean way of doing this and getting SQL Server to short circuit the scan as soon as the total is reached.

You could use a recursive CTE to add up the balances instead. The exit condition for this could be once the balance running total exceeds 50,000. Dependant on table cardinality and indexes this could be either more or less efficient.

You could also have a custom CLR aggregate that terminated processing by throwing an error if 50,000 was reached but quite a horrible solution!

For versions since 2012 see my answer here

---

You can't stop the query unless you create a procedure with a cursor, which will stop fetching after reaching 50000 (don't know if it's worthing, but it's the only way, maybe you have millions of rows):

DECLARE @bal int
DECLARE @bal_tot int
DECLARE cur CURSOR FOR 
SELECT sum(balance)
FROM account
WHERE balance > 2000
ORDER BY balance DESC;

OPEN cur;

FETCH NEXT FROM cur
INTO @bal
WHILE @@FETCH_STATUS = 0 AND @bal_tot < 50000
BEGIN
@bal_tot = @bal_tot + @bal
END
CLOSE cur;

if @bal_tot > 50000 etc...

---

Look at the HAVING keyword.

SELECT sum(balance)
FROM account
WHERE balance > 2000
HAVING sum(balance) > 50000

Edit: This likely doesn't shortcut like the original question asked. It might if the the SQL Server query engine realized the where/having clauses can be shortcut (as Martin Smith said in a comment). I would think writing your own shortcut-er would be overly complicated, and if you're hitting performance issues there would likely be better approaches.

If you need a true/false answer via a query, then Jim Dagg's answer looks good to me.

---

I wouldn't go the route of trying to write a complicated procedure to short-circuit the query. -- that's overengineering the problem. Honestly, just pulling the sum and doing the comparison the way you're doing now is the most straightforward method. If you want to select the result of the comparison for use in other SQL processing, you could try this:

SELECT CASE WHEN 
    (SELECT SUM(AmtCurrent)
     FROM [SisMaster].[dbo].[Loan]
     WHERE AmtCurrent > 2000) > 50000 
THEN 1 
ELSE 0 
END

---

SELECT CASE WHEN  TotalAmtCurrent > 50000  THEN 1  ELSE 0  END 
FROM
    (SELECT SUM(AmtCurrent)  as TotalAmtCurrent    
    FROM [SisMaster].[dbo].[Loan]     
    WHERE AmtCurrent > 2000) a