title: Sql Trend line by departments
body: <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>