Reputation: 3
Is it possible to make some optimization on such query ?
with t (CurrentPrice, [Weight])
as ( select CurrentPrice, [Weight] from SomeTable where GroupingId = SomeBigIntId )
SELECT MaxExactPrice = ( select MAX(CurrentPrice) FROM t Where [Weight] > 0 ),
MinExactPrice = ( select MIN(CurrentPrice) FROM t Where [Weight] > 0 ),
MaxSimilarPrice = ( select MAX(CurrentPrice) FROM t Where [Weight] = 0 ),
MinSimilarPrice = ( select MIN(CurrentPrice) FROM t Where [Weight] = 0 ),
ExactCount = ( select Count(*) FROM t Where [Weight] > 0 ),
SimilarCount = ( select Count(*) FROM t Where [Weight] = 0 ),
Count(*) as TotalCount
FROM t
Thank you.
Upvotes: 0
Views: 2815
Reputation:
Do this instead. this way you do not have to hit same table 8 different times.
select MAX(CASE WHEN [Weight] > 0 THEN CurrentPrice ELSE NULL END) AS MaxExactPrice
,MIN(CASE WHEN [Weight] > 0 THEN CurrentPrice ELSE NULL END) AS MinExactPrice
,MAX(CASE WHEN [Weight] = 0 THEN CurrentPrice ELSE NULL END) AS MaxSimilarPrice
,MIN(CASE WHEN [Weight] = 0 THEN CurrentPrice ELSE NULL END) AS MinSimilarPrice
,COUNT(CASE WHEN [Weight] > 0 THEN 1 ELSE NULL END ) AS ExactCount
,COUNT(CASE WHEN [Weight] = 0 THEN 1 ELSE NULL END ) AS SimilarCount
,COUNT(*)AS TotalCount
from SomeTable where GroupingId = SomeBigIntId
Upvotes: 3