CODEWITHSUNDEEP
sql-server
jeet
jeet

Reputation: 37

How to optimize the sql query with aggregate function?

Below is the sql query i want to optimize.

 SELECT (ISNULL((SELECT SUM(Amount) FROM tblPayment WHERE TransactionType = 1 and AccountType = 2 and Status = 3),0) 
         +
         ISNULL((SELECT SUM(Amount) FROM tblPayment WHERE TransactionType = 2 and AccountType = 2 and Status = 3),0)
        ) 'Total Funds'

How can i optimize it in a better way ?

Thanks for any suggestions!

Upvotes: 0

Views: 341

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Instead of having two different queries to sum TransactionType = 1 and TransactionType = 2 use TransactionType in (1,2) 

SELECT Isnull(Sum(Amount),0)
FROM   tblPayment
WHERE  TransactionType IN( 1, 2 )
       AND AccountType = 2
       AND Status = 3

Upvotes: 1

Steve Ford
Steve Ford

Reputation: 7763

try:

SELECT COALESCE(SUM(Amount), 0) AS [Total Funds]
FROM tblPayment
WHERE AccountType = 2 AND Status = 3 
      AND TransactionType IN (1,2)

Upvotes: 0

Related Questions