CODEWITHSUNDEEP
sqlsql-server-2008
masteroleary
masteroleary

Reputation: 1034

How do i avoid calling the same sql statement over and over

I have a sql statement that says 

SELECT coalesce((Select sum(SomeNumber) 
FROM Table 
WHERE ID NOT IN (SELECT IDs...)), 0) MyFirstNumber,
coalesce((Select sum(SomeNumber2) 
FROM Table 
WHERE ID NOT IN (SELECT IDs...)), 0) MySecondNumber

How can I make the (SELECT IDs...) query statement be called only once, say before the statement above.

I think it would be something like 

DECLARE @MyIDs
SET @MyIDs = SELECT IDs FROM TABLE WHERE ...

SELECT coalesce((Select sum(SomeNumber) 
FROM Table 
WHERE ID NOT IN (@MyIDs)), 0) MyFirstNumber,
coalesce((Select sum(SomeNumber2) 
FROM Table 
WHERE ID NOT IN (@MyIDs)), 0) MySecondNumber

Upvotes: 0

Views: 127

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Calculate the amount in one SELECT statement

SELECT COALESCE(SUM(SomeNumber), 0) AS MyFirstNumber, 
       COALESCE(SUM(SomeNumber2), 0) AS MySecondNumber
FROM Table
WHERE ID NOT IN (SELECT IDs FROM TABLE WHERE...)

If need different conditions you can use CASE expression within SUM() functions

SELECT COALESCE(SUM(SomeNumber), 0) AS MyFirstNumber, 
       COALESCE(SUM(SomeNumber2), 0) AS MySecondNumber,
       COALESCE(SUM(CASE WHEN ... THEN SomeNumber3 END), 0) AS MyThirdNumber
FROM Table
WHERE ID NOT IN (SELECT IDs FROM TABLE WHERE...)

Upvotes: 0

CaldasGSM
CaldasGSM

Reputation: 3062

how about joining with that subselect and fitering only where the join is false ( returns null)

SELECT sum(coalesce(SomeNumber,0)) MyFirstNumber
       ,sum(coalesce(SomeNumber2,0)) MySecondNumber
FROM   Table 
       LEFT JOIN (SELECT IDs.. FROM Table ) AS Filter ON Table.ID = Filter.ID
WHERE  Filter.ID IS NULL

Upvotes: 1

Related Questions