Sql Trend line by departments
<p>I'm using the example of how to create a sql trend line on a report using the below link. </p>

<p><a href="https://www.mssqltips.com/sqlservertip/3432/add-a-linear-trendline-to-a-graph-in-sql-server-reporting-services/" rel="nofollow">https://www.mssqltips.com/sqlservertip/3432/add-a-linear-trendline-to-a-graph-in-sql-server-reporting-services/</a> </p>

<p>I've got it all up and running but I want to work out the trend by departments also. However its just merging all the data into one final value, I think its the below section of code that needs altering to calculate the sum by each of the departments I add in, but how best do I do this?</p>

<pre><code>-- calculate sample size and the different sums
SELECT
  @sample_size = COUNT(*)
 ,@sumX   = SUM(ID)
 ,@sumY   = SUM([OrderQuantity])
 ,@sumXX   = SUM(ID*ID)
 ,@sumYY   = SUM([OrderQuantity]*[OrderQuantity])
 ,@sumXY   = SUM(ID*[OrderQuantity])
FROM #Temp_Regression;

-- output results
SELECT
  SampleSize   = @sample_size  
 ,SumRID    = @sumX   
 ,SumOrderQty   =@sumY   
 ,SumXX    = @sumXX   
 ,SumYY    = @sumYY   
 ,SumXY    = @sumXY;
</code></pre>

<p>These variables are then used to work out the trend line:</p>

<pre><code>-- calculate the slope and intercept
SET @slope = CASE WHEN @sample_size = 1
    THEN 0 -- avoid divide by zero error
    ELSE (@sample_size * @sumXY - @sumX * @sumY) / (@sample_size * @sumXX - POWER(@sumX,2))
    END;
SET @intercept = (@sumY - (@slope*@sumX)) / @sample_size;
</code></pre>