with group by vs without: different result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

with group by vs without: different result

Author

Topic

kirilb
Starting Member

1 Post

Posted - 2014-07-04 : 04:19:31

I have to optimize an old query, which contains a lot of case when conditions in the select and group by. It has just one figure(fact) and rest are dimensions. The optimized version has exactly the same where part, so same filter. Nevertheless when I sum up this one figure for the whole output, just to check both versions, I get slightly higher figure with the old version. How is that possible when the filter is the same? Can the case when conditions in the group by somehow be responsible for double counting? Pls see below examples for both versions:

New version:

select
category,
figure
from A
where
sameCondition

Old version:

select
max(category),
sum(figure)
from A
where
sameCondition
group by
case when1,
...
case when100000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-04 : 04:48:49

The sum of all SUM(Figure) in the old version should be the same as the sum of all Figure in the new version.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources