CODEWITHSUNDEEP
sqloptimizationaggregation
Joel Shemtov
Joel Shemtov

Reputation: 3078

Optimise aggregation query

I'm looking for a way to optimise the following:

SELECT 
    (SELECT SUM(amount) FROM Txn_Log WHERE gid=@gid AND txnType IN (3, 20)) AS pendingAmount,
    (SELECT COUNT(1) FROM Txn_Log WHERE gid = @gid AND txnType = 11) AS pendingReturn,
    (SELECT COUNT(1) FROM Txn_Log WHERE gid = @gid AND txnType = 5) AS pendingBlock

where @gid is a parameter and gid is an index field on this table. Problem: each sub-query reruns on the same set of entries - three reruns are two too many.

Upvotes: 1

Views: 182

Answers (2)

Ron Tuffin
Ron Tuffin

Reputation: 54619

Can you not do something like this

SELECT sum(amount),count(1), txnType
FROM Txn_log
WHERE gid = @gid AND
    txnType in (3,5,11,20)
group by txnType

and then handle the rest of it programmatically?

Upvotes: 1

Guffa
Guffa

Reputation: 700352

You can do like this:

select
   sum(case when txnType in (3,20) then amount else 0 end) as pendingAmount,
   sum(case txnType when 11 then 1 else 0 end) as pendingReturn,
   sum(case txnType when 5 then 1 else 0 end) as pendingBlock
from
   Txn_Log
where
   gid = @gid

Upvotes: 4

Related Questions