optimising aggregates with and without a condition in ms sql
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.
精彩评论