# SQL How to Forecast with Linear Regression

## What

Solving the SLOPE and INTERCEPT parameters of a linear model equation with one variable is easy with some basic SQL. Here I give an example Code which shows too how to use the calculated SLOPE and INTERCEPT to make forecasts.

## Code

```--calculation of the linear model coefficients and saving them INTO linear_models table
--the original data FROM actual_consumption_data_table has in addition a date_id dimension, separate values for every date

SELECT
[dimension_1],
[dimension_2],
((sum([actual_temperature]) * sum([average consumption])) - (count(1) * sum([actual_temperature] * [average consumption]))) /
NULLIF((power(sum([actual_temperature]), 2) - count(1) * sum(power([actual_temperature], 2))),0) as SLOPE,

((sum([actual_temperature]) * sum([actual_temperature] * [average consumption])) -
(sum([average consumption]) * sum(power([actual_temperature], 2)))) /
NULLIF((power(sum([actual_temperature]), 2) - count(1) * sum(power([actual_temperature], 2))),0) as INTERCEPT
INTO linear_models_table
FROM actual_consumption_data_table
GROUP BY
dimension_1,
dimension_2

--using the coefficients to make for example a normalized average consumption curve:
select
a.[date_id],
a.[dimension_1],
a.[dimension_2],
a.[normal_temperature]*b.[SLOPE] + b.[INTERCEPT]
from actual_consumption_data_table a
left join linear_models_table b on a.[dimension_1] = b.[dimension_1]
and a.[dimension_2] = b.[dimension_2]```