user3691566 - 5 months ago 32

SQL Question

I'm using the example of how to create a sql trend line on a report using the below link.

https://www.mssqltips.com/sqlservertip/3432/add-a-linear-trendline-to-a-graph-in-sql-server-reporting-services/

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?

`-- 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;

These variables are then used to work out the trend line:

`-- 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;

Answer

You need to add `departments`

column in `SELECT`

& `GROUP BY`

```
SELECT departments,
SampleSize = Count(*),
SumRID = Sum(ID),
SumOrderQty = Sum([OrderQuantity]),
SumXX = Sum(ID * ID),
SumYY = Sum([OrderQuantity] * [OrderQuantity]),
SumXY = Sum(ID * [OrderQuantity])
FROM #Temp_Regression
GROUP BY departments
```

Here is the easier way to calculate `slope`

& `intercept`

for all departments

```
;WITH cte
AS (SELECT departments,
sample_size = Count(*),
sumX = Sum(ID),
sumY = Sum([OrderQuantity]),
sumXX = Sum(ID * ID),
sumYY = Sum([OrderQuantity] * [OrderQuantity]),
sumXY = Sum(ID * [OrderQuantity])
FROM #Temp_Regression
GROUP BY departments),
slope
AS (SELECT departments,
Sample_Size,
sumX,
sumY,
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
FROM cte)
SELECT departments,
slope,
intercept = ( sumY - ( slope * sumX ) ) / sample_size
FROM slope
```