开发者

optimising aggregates with and without a condition in ms sql

开发者 https://www.devze.com 2023-01-19 05:34 出处:网络
I would like to get a sum from a column, with and without a condition. The code I have now is SELECT regular.id, regular.sum as regularsum, special.sum as specialsum FROM

I would like to get a sum from a column, with and without a condition. The code I have now is

SELECT regular.id, regular.sum as regularsum, special.sum as specialsum FROM 
(SELECT Sum(stuff) as sum, id FROM table
 WHERE commonCondition = true
 GROUP BY id) as regular
INNER JOIN开发者_StackOverflow中文版 
(SELECT Sum(stuff) as sum, id FROM table
 Where commonCondition = true AND specialCondition = true
 GROUP BY id) as special
ON regular.id = special.id

Which works, but seems very inefficient, not to mention ugly. the table is fairly large, so some optimisation would be welcome.

How can I write this in a better, more efficent way?


I think you could do something like this:

 SELECT 
    Sum(stuff) as regularsum, 
    sum(case when specialcondition=true then stuff else 0 end) as specialsum,
    id FROM table
 WHERE commonCondition = true
 GROUP BY id

However, you'd want to test to see if it was any faster.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号