Juan Velasquez
asked on
Order of Precedence in T-SQL
In the code below, it appears that I have a different result whenever I change the order of the element above the denominator. Since these are just simple addition and subtraction operators why would it make a difference how they are ordered. I was finally able to get it woking properly but I am a bit confused about the operatior order of precedence.
(SELECT DV.ForecastYear, @Metric1,
( Sum(case when MetricName=@Metric1 then MetricValue end)
+ Sum(case when MetricName=@Metric3 then MetricValue end)
+ Sum(case when MetricName=@Metric4 then MetricValue end)
- Sum(case when MetricName=@Metric2 then MetricValue end)
+ Sum(case when MetricName=@Metric5 then MetricValue end)
- Sum(case when MetricName=@Metric6 then MetricValue end)
) / Sum(case when MetricName=@Metric7 then MetricValue end) CalcResults, @spid
FROM Metrics M2
INNER JOIN DataVersion DV
ON M2.DataVersionRowID = DV.DataVersionRowID
WHERE DV.DataVersionID = @DVID
AND MetricName in (@Metric1, @Metric2, @Metric3, @Metric4, @Metric5, @Metric6, @Metric7)
Group by DV.ForecastYear)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just a thought, if @Metric1 through 6 are always DISTINCT, this should simplify it
Sum(case when MetricName in (@Metric1,@Metric3,@Metric 4,@Metric5 ) then MetricValue end)
- Sum(case when MetricName in (@Metric2,@Metric6) then MetricValue end)
But re your question, the order should not have mattered as all if that is your full query.
Sum(case when MetricName in (@Metric1,@Metric3,@Metric
- Sum(case when MetricName in (@Metric2,@Metric6) then MetricValue end)
But re your question, the order should not have mattered as all if that is your full query.
I suggest you to do the aggregations first and then apply the calculations.
SELECT ForecastYear,
Metric1,
(sum_1 + sum_2 + sum_3 - sum_4 + sum_5 - sum_6) / sum_7,
spid
FROM ( SELECT DV.ForecastYear,
@Metric1 Metric1,
SUM(CASE
WHEN MetricName = @Metric1 THEN MetricValue
END) sum_1,
SUM(CASE
WHEN MetricName = @Metric3 THEN MetricValue
END) sum_2,
SUM(CASE
WHEN MetricName = @Metric4 THEN MetricValue
END) sum_3,
SUM(CASE
WHEN MetricName = @Metric2 THEN MetricValue
END) sum_4,
SUM(CASE
WHEN MetricName = @Metric5 THEN MetricValue
END) sum_5,
SUM(CASE
WHEN MetricName = @Metric6 THEN MetricValue
END) sum_6,
SUM(CASE
WHEN MetricName = @Metric7 THEN MetricValue
END) sum_7,
@spid spid
FROM Metrics M2
INNER JOIN DataVersion DV
ON M2.DataVersionRowID = DV.DataVersionRowID
WHERE DV.DataVersionID = @DVID
AND MetricName IN (@Metric1,@Metric2,@Metric3,@Metric4,
@Metric5,@Metric6,@Metric7)
GROUP BY DV.ForecastYear) t1
It really shouldnt matter. With divides, you need to be careful of using integers rather than decimals, so sometimes do a * 1.0 to ensure it uses decimal arithmatic.
The only thing I can think of off the top of my head is maybe NULL values upsetting the aggregate functions.
Using your original, and by the way both cyberkiwi and sharath have good suggestions above...
( Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
)
-- should be the same as
( Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
)
also your denominator should also be checked, not just for "else 0" but also that it is in fact not zero.
The only thing I can think of off the top of my head is maybe NULL values upsetting the aggregate functions.
Using your original, and by the way both cyberkiwi and sharath have good suggestions above...
( Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
)
-- should be the same as
( Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
)
also your denominator should also be checked, not just for "else 0" but also that it is in fact not zero.
ASKER
Thanks for the useful reference
Interesting choice - does that help explain your question ? Or just a useful reference ?
Whilst a good link, think all it was saying is what the other experts actually said - it should not matter.
The only time it might matter is starting off with subtracting - you have to subtract from something (or could " + (something * -1) " )
check below for what I was saying about NULLs - two scenarios, one is NULL metricvalue and the other is missing metricnames.
There are settings, and if running in batch mode might be needed to avoid an abort.
But anyway, just wanted to make sure you knew the problems with handling NULL values.
Whilst a good link, think all it was saying is what the other experts actually said - it should not matter.
The only time it might matter is starting off with subtracting - you have to subtract from something (or could " + (something * -1) " )
check below for what I was saying about NULLs - two scenarios, one is NULL metricvalue and the other is missing metricnames.
There are settings, and if running in batch mode might be needed to avoid an abort.
But anyway, just wanted to make sure you knew the problems with handling NULL values.
if object_id('tempdb..#mydata_source') is not null drop table #mydata_source
create table #mydata_source (Forecastyear int, MetricName varchar(20), metricValue decimal (18,6))
insert #mydata_source values (2009,'M2',222)
insert #mydata_source values (2009,'M5',555)
insert #mydata_source values (2009,'M6',666)
insert #mydata_source values (2009,'M7',777)
insert #mydata_source values (2010,'M1',111.111)
insert #mydata_source values (2010,'M2',222.222)
insert #mydata_source values (2010,'M3',333.333)
insert #mydata_source values (2010,'M4',444.444)
insert #mydata_source values (2010,'M5',555.555)
insert #mydata_source values (2010,'M7',777.777)
--insert #mydata_source values (2010,'M6',666.66)
insert #mydata_source values (2010,'M6',NULL)
declare @metric1 varchar(20)
declare @metric2 varchar(20)
declare @metric3 varchar(20)
declare @metric4 varchar(20)
declare @metric5 varchar(20)
declare @metric6 varchar(20)
declare @metric7 varchar(20)
set @metric1 = 'M1'
set @metric2 = 'M2'
set @metric3 = 'M3'
set @metric4 = 'M4'
set @metric5 = 'M5'
set @metric6 = 'M6'
set @metric7 = 'M7'
select forecastyear,
( Sum(case when MetricName=@Metric1 then MetricValue end)
+ Sum(case when MetricName=@Metric3 then MetricValue end)
+ Sum(case when MetricName=@Metric4 then MetricValue end)
- Sum(case when MetricName=@Metric2 then MetricValue end)
+ Sum(case when MetricName=@Metric5 then MetricValue end)
- Sum(case when MetricName=@Metric6 then MetricValue end)
) as old_way,
( Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
) as new_way,
( 0
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
) as new_way_jumbled
from #mydata_source
group by forecastyear
There should be no difference whatsoever however you order these lines:
Sum(case when MetricName=@Metric1 then MetricValue end)
+ Sum(case when MetricName=@Metric3 then MetricValue end)
+ Sum(case when MetricName=@Metric4 then MetricValue end)
- Sum(case when MetricName=@Metric2 then MetricValue end)
+ Sum(case when MetricName=@Metric5 then MetricValue end)
- Sum(case when MetricName=@Metric6 then MetricValue end)