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)

Are you sure you have no other changes except the order?
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)

Just a thought, if @Metric1 through 6 are always DISTINCT, this should simplify it

Sum(case when MetricName in (@Metric1,@Metric3,@Metric4,@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.

0

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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.

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …

Hi all,
It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…

Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video.
If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …