use mytools;

DROP TABLE IF EXISTS pasta_prices ;
CREATE TABLE pasta_prices ( kilos INT(3), dollars INT(3));

-- approximate line: dollars = 2*kilos+3
INSERT INTO pasta_prices VALUES (5,12);
INSERT INTO pasta_prices VALUES (6,16);
INSERT INTO pasta_prices VALUES (7,17);
INSERT INTO pasta_prices VALUES (8,20);
INSERT INTO pasta_prices VALUES (9,22);
INSERT INTO pasta_prices VALUES (10,23);
INSERT INTO pasta_prices VALUES (11,25);

SELECT
 @num   := COUNT(dollars)         AS Num,
 @meanX := format(AVG(kilos),3)   AS "XMean",
 @sumX  := SUM(kilos)             AS "XSum",
 @sumXS := SUM(kilos*kilos)       AS "XSumOfSquares",
 @meanY := format(AVG(dollars),3) AS "YMean",
 @sumY  := SUM(dollars)           AS "SumOfY",
 @sumYS := SUM(dollars*dollars)   AS "YSumOfSquares",
 @sumXY := SUM(kilos*dollars)     AS "SumOfX*Y"
 FROM pasta_prices;

SELECT
 @m := format((@n*@sumXY - @sumX*@sumY) / (@num*@sumXS - @sumX*@sumX),3)
 AS slope;

SELECT @b := format((@meanY - @m*@meanX),3) AS intercept; 

SELECT CONCAT('Y = ',@m,'X + ',@b) AS 'Least Squares Regression';

SELECT
 format((@num*@sumXY - @sumX*@sumY)
 / SQRT((@num*@sumXS - @sumX*@sumX) * (@num*@sumYS - @sumY*@sumY)),4)
 AS correlation;

