Moving average

Moving average

Hello guys, I’ve problem with MySQL and need your help.

I attach the file xlsx in the test.zip.

This file xlsx is able to calculate the moving average (column G - AVERAGE_12_MONTHS) of the column F (F_SUM).

The average is dynamic because in the column G is a line feed, e.g.:

In the cell G13 I’ve the average of cell F2:F13, in the cell G14 the average is of cells F3:F14, in the cell G15 the average is of cells F4:F15… etc

I need realize the same average in mysql, any suggestion?
Can you help me?
Any help would be very appreciated

  1. i cannot read zip files
  2. i don’t want to read xlsx files

please restate your question in terms of database tables

Ok, I understand.

In this table page1-1 I need:

  1. In column F_SUM update with sum of colums values F1, F2 and F3
  2. In column calculate AVERAGE_12_MONTHS the dynamic average of the column F_SUM

The average is dynamic because in the column AVERAGE_12_MONTHS is a line feed, e.g.:

  • In the record number 12 I’ve the average of records 1,2,3,4,5,6,7,8,9,10,11 and 12
  • In the record number 13 I’ve the average of records 2,3,4,5,6,7,8,9,10,11,12 and 13
  • In the record number 14 I’ve the average of records 3,4,5,6,7,8,9,10,11,12,13 and 14
-- ----------------------------
-- Table structure for `page1-1`
-- ----------------------------
DROP TABLE IF EXISTS `page1-1`;
CREATE TABLE `page1-1` (
  `YEARS` varchar(255) DEFAULT NULL,
  `MONTHS` varchar(255) DEFAULT NULL,
  `F1` varchar(255) DEFAULT NULL,
  `F2` varchar(255) DEFAULT NULL,
  `F3` varchar(255) DEFAULT NULL,
  `F_SUM` varchar(255) DEFAULT NULL,
  `AVERAGE_12_MONTHS` varchar(255) DEFAULT NULL,
  `VAR` varchar(255) DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of page1-1
-- ----------------------------
INSERT INTO `page1-1` VALUES ('2009', '05', '92.626,4', '32.796,56', '59.924,640002', '', '', '', '1');
INSERT INTO `page1-1` VALUES ('2009', '06', '95.008,64', '31.933,6', '56.977,839993', '', '', '', '2');
INSERT INTO `page1-1` VALUES ('2009', '07', '111.853,12', '33.091,92', '53.262,400004', '', '', '', '3');
INSERT INTO `page1-1` VALUES ('2009', '08', '108.379,92', '32.500,48', '60.452,640005', '', '', '', '4');
INSERT INTO `page1-1` VALUES ('2009', '09', '102.605,44', '34.639,36', '58.108,399992', '', '', '', '5');
INSERT INTO `page1-1` VALUES ('2009', '10', '83.442,56', '31.952,400002', '52.181,6', '', '', '', '6');
INSERT INTO `page1-1` VALUES ('2009', '11', '55.527,84', '24.864', '47.962,800004', '', '', '', '7');
INSERT INTO `page1-1` VALUES ('2009', '12', '45.067,28', '21.627,28', '50.793,6', '', '', '', '8');
INSERT INTO `page1-1` VALUES ('2010', '01', '50.108,6386', '32.070,35916', '88.992,39535', '', '', '', '9');
INSERT INTO `page1-1` VALUES ('2010', '02', '45.229,00261', '32.119,99696', '57.848,00256', '', '', '', '10');
INSERT INTO `page1-1` VALUES ('2010', '03', '51.329,99474', '34.104,99888', '64.472,82168', '', '', '', '11');
INSERT INTO `page1-1` VALUES ('2010', '04', '47.097,99864', '38.268,00225', '77.124,0064', '', '', '', '12');
INSERT INTO `page1-1` VALUES ('2010', '05', '43.667,00318', '31.968,99992', '108.472,0064', '', '', '', '13');
INSERT INTO `page1-1` VALUES ('2010', '06', '55.458,99688', '32.521,00124', '103.608', '', '', '', '14');
INSERT INTO `page1-1` VALUES ('2010', '07', '99.716,0032', '33.510,99578', '74.126,99424', '', '', '', '15');
INSERT INTO `page1-1` VALUES ('2010', '08', '101.051,99552', '31.377,0024', '67.117,99712', '', '', '', '16');
INSERT INTO `page1-1` VALUES ('2010', '09', '72.834,00258', '35.291,00016', '81.023,00416', '', '', '', '17');
INSERT INTO `page1-1` VALUES ('2010', '10', '42.791,00364', '44.317,0016', '87.538,9998', '', '', '', '18');
INSERT INTO `page1-1` VALUES ('2010', '11', '41.505,99693', '32.237,00012', '66.770,9952', '', '', '', '19');
INSERT INTO `page1-1` VALUES ('2010', '12', '38.599,005', '27.211,99872', '61.212,99744', '', '', '', '20');
INSERT INTO `page1-1` VALUES ('2011', '01', '55.458,99761', '28.852,99888', '60.279,0048', '', '', '', '21');
INSERT INTO `page1-1` VALUES ('2011', '02', '46.786,00489', '29.952,00224', '58.459,00032', '', '', '', '22');
INSERT INTO `page1-1` VALUES ('2011', '03', '52.550,00568', '32.447,00064', '64.910,76056', '', '', '', '23');
INSERT INTO `page1-1` VALUES ('2011', '04', '39.288,9992', '41.818,0032', '81.383,00113', '', '', '', '24');
INSERT INTO `page1-1` VALUES ('2011', '05', '84.902,99957', '32.890,002', '66.315,99776', '', '', '', '25');
INSERT INTO `page1-1` VALUES ('2011', '06', '94.883,73246', '35.711,45084', '60.993,83012', '', '', '', '26');
INSERT INTO `page1-1` VALUES ('2011', '07', '113.265,999', '38.767,0016', '55.319,98993', '', '', '', '27');
INSERT INTO `page1-1` VALUES ('2011', '08', '122.653,8295', '28.179,25128', '48.713,92516', '', '', '', '28');
INSERT INTO `page1-1` VALUES ('2011', '09', '118.939,00216', '25.380,0064', '44.827,99808', '', '', '', '29');
INSERT INTO `page1-1` VALUES ('2011', '10', '72.671,00321', '35.154,0034', '66.821,9968', '', '', '', '30');
INSERT INTO `page1-1` VALUES ('2011', '11', '52.688,03987', '29.032,06384', '58.792,89148', '', '', '', '31');
INSERT INTO `page1-1` VALUES ('2011', '12', '50.778,1824', '24.184,41893', '52.061,39352', '', '', '', '32');
INSERT INTO `page1-1` VALUES ('2012', '01', '67.452', '27.755,99892', '49.381,99808', '', '', '', '33');
INSERT INTO `page1-1` VALUES ('2012', '02', '65.157,00029', '25.064,9984', '44.974,99776', '', '', '', '34');
INSERT INTO `page1-1` VALUES ('2012', '03', '51.895,99976', '35.507,99695', '62.303,66252', '', '', '', '35');
INSERT INTO `page1-1` VALUES ('2012', '04', '59.680,0026', '34.401,99684', '68.407,00416', '', '', '', '36');
INSERT INTO `page1-1` VALUES ('2012', '05', '76.293,00236', '36.553,00128', '71.262,99872', '', '', '', '37');
INSERT INTO `page1-1` VALUES ('2012', '06', '91.630,00308', '36.862,00284', '63.096,99387', '', '', '', '38');
INSERT INTO `page1-1` VALUES ('2012', '07', '121.355,00758', '35.470,99968', '50.525,99904', '', '', '', '39');
INSERT INTO `page1-1` VALUES ('2012', '08', '104.053,9946', '38.849,00088', '56.643,00224', '', '', '', '40');
INSERT INTO `page1-1` VALUES ('2012', '09', '87.243,99989', '38.957,9976', '62.945,9968', '', '', '', '41');
INSERT INTO `page1-1` VALUES ('2012', '10', '75.514,00438', '35.562,0016', '63.570,9984', '', '', '', '42');
INSERT INTO `page1-1` VALUES ('2012', '11', '47.608,00044', '30.661,00425', '62.245,0048', '', '', '', '43');
INSERT INTO `page1-1` VALUES ('2012', '12', '13.527,2', '10.924,04032', '26.763,04036', '', '', '', '44');
INSERT INTO `page1-1` VALUES ('2013', '01', '15.955,44', '9.888,8', '18.707,36', '', '', '', '45');
INSERT INTO `page1-1` VALUES ('2013', '02', '14.813,76', '9.452,24', '17.036,08', '', '', '', '46');
INSERT INTO `page1-1` VALUES ('2013', '03', '13.567,92', '9.950,64', '18.263,44', '', '', '', '47');
INSERT INTO `page1-1` VALUES ('2013', '04', '12.965,6', '8.086', '17.543,6', '', '', '', '48');
INSERT INTO `page1-1` VALUES ('2013', '05', '12.363,76', '8.034,64', '16.605,04', '', '', '', '49');
INSERT INTO `page1-1` VALUES ('2013', '06', '15.822,88', '8.388,88', '16.211,92', '', '', '', '50');
INSERT INTO `page1-1` VALUES ('2013', '07', '21.269,04', '9.082,4', '16.352,48', '', '', '', '51');
INSERT INTO `page1-1` VALUES ('2013', '08', '18.727,44', '10.274,16', '18.108,96', '', '', '', '52');
INSERT INTO `page1-1` VALUES ('2013', '09', '16.470,56', '8.159,76', '16.271,92', '', '', '', '53');

let’s start with this problem

UPDATE `page1-1` 
   SET F_sum = F1 + F2 + F3 

this gives the error message “Truncated incorrect DOUBLE value 59.924,640002”

i think the problem is your use of VARCHAR(255) for numeric values

thank you for help.
this is new version of table page1-1.
I’ve update in column F_SUM with sum of colums values F1, F2 and F3.

But I can not calculate the average dynamic… because in the column AVERAGE_12_MONTHS is a line feed, e.g.:

  • In the record number 12 I’ve the average of records 1,2,3,4,5,6,7,8,9,10,11 and 12
  • In the record number 13 I’ve the average of records 2,3,4,5,6,7,8,9,10,11,12 and 13
  • In the record number 14 I’ve the average of records 3,4,5,6,7,8,9,10,11,12,13 and 14
-- ----------------------------
-- Table structure for `page1-1`
-- ----------------------------
DROP TABLE IF EXISTS `page1-1`;
CREATE TABLE `page1-1` (
  `YEARS` int(11) DEFAULT NULL,
  `MONTHS` int(11) DEFAULT NULL,
  `F1` decimal(10,4) DEFAULT NULL,
  `F2` decimal(10,4) DEFAULT NULL,
  `F3` decimal(10,4) DEFAULT NULL,
  `F_SUM` varchar(255) DEFAULT NULL,
  `AVERAGE_12_MONTHS` varchar(255) DEFAULT NULL,
  `VAR` varchar(255) DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of page1-1
-- ----------------------------
INSERT INTO `page1-1` VALUES ('2009', '5', '92626.4000', '32796.5600', '59924.6400', '185347.6000', '', '', '1');
INSERT INTO `page1-1` VALUES ('2009', '6', '95008.6400', '31933.6000', '56977.8400', '183920.0800', '', '', '2');
INSERT INTO `page1-1` VALUES ('2009', '7', '111853.1200', '33091.9200', '53262.4000', '198207.4400', '', '', '3');
INSERT INTO `page1-1` VALUES ('2009', '8', '108379.9200', '32500.4800', '60452.6400', '201333.0400', '', '', '4');
INSERT INTO `page1-1` VALUES ('2009', '9', '102605.4400', '34639.3600', '58108.4000', '195353.2000', '', '', '5');
INSERT INTO `page1-1` VALUES ('2009', '10', '83442.5600', '31952.4000', '52181.6000', '167576.5600', '', '', '6');
INSERT INTO `page1-1` VALUES ('2009', '11', '55527.8400', '24864.0000', '47962.8000', '128354.6400', '', '', '7');
INSERT INTO `page1-1` VALUES ('2009', '12', '45067.2800', '21627.2800', '50793.6000', '117488.1600', '', '', '8');
INSERT INTO `page1-1` VALUES ('2010', '1', '50108.6386', '32070.3592', '88992.3954', '171171.3932', '', '', '9');
INSERT INTO `page1-1` VALUES ('2010', '2', '45229.0026', '32119.9970', '57848.0026', '135197.0022', '', '', '10');
INSERT INTO `page1-1` VALUES ('2010', '3', '51329.9947', '34104.9989', '64472.8217', '149907.8153', '', '', '11');
INSERT INTO `page1-1` VALUES ('2010', '4', '47097.9986', '38268.0023', '77124.0064', '162490.0073', '', '', '12');
INSERT INTO `page1-1` VALUES ('2010', '5', '43667.0032', '31968.9999', '108472.0064', '184108.0095', '', '', '13');
INSERT INTO `page1-1` VALUES ('2010', '6', '55458.9969', '32521.0012', '103608.0000', '191587.9981', '', '', '14');
INSERT INTO `page1-1` VALUES ('2010', '7', '99716.0032', '33510.9958', '74126.9942', '207353.9932', '', '', '15');
INSERT INTO `page1-1` VALUES ('2010', '8', '101051.9955', '31377.0024', '67117.9971', '199546.9950', '', '', '16');
INSERT INTO `page1-1` VALUES ('2010', '9', '72834.0026', '35291.0002', '81023.0042', '189148.0070', '', '', '17');
INSERT INTO `page1-1` VALUES ('2010', '10', '42791.0036', '44317.0016', '87538.9998', '174647.0050', '', '', '18');
INSERT INTO `page1-1` VALUES ('2010', '11', '41505.9969', '32237.0001', '66770.9952', '140513.9922', '', '', '19');
INSERT INTO `page1-1` VALUES ('2010', '12', '38599.0050', '27211.9987', '61212.9974', '127024.0011', '', '', '20');
INSERT INTO `page1-1` VALUES ('2011', '1', '55458.9976', '28852.9989', '60279.0048', '144591.0013', '', '', '21');
INSERT INTO `page1-1` VALUES ('2011', '2', '46786.0049', '29952.0022', '58459.0003', '135197.0074', '', '', '22');
INSERT INTO `page1-1` VALUES ('2011', '3', '52550.0057', '32447.0006', '64910.7606', '149907.7669', '', '', '23');
INSERT INTO `page1-1` VALUES ('2011', '4', '39288.9992', '41818.0032', '81383.0011', '162490.0035', '', '', '24');
INSERT INTO `page1-1` VALUES ('2011', '5', '84902.9996', '32890.0020', '66315.9978', '184108.9994', '', '', '25');
INSERT INTO `page1-1` VALUES ('2011', '6', '94883.7325', '35711.4508', '60993.8301', '191589.0134', '', '', '26');
INSERT INTO `page1-1` VALUES ('2011', '7', '113265.9990', '38767.0016', '55319.9899', '207352.9905', '', '', '27');
INSERT INTO `page1-1` VALUES ('2011', '8', '122653.8295', '28179.2513', '48713.9252', '199547.0060', '', '', '28');
INSERT INTO `page1-1` VALUES ('2011', '9', '118939.0022', '25380.0064', '44827.9981', '189147.0067', '', '', '29');
INSERT INTO `page1-1` VALUES ('2011', '10', '72671.0032', '35154.0034', '66821.9968', '174647.0034', '', '', '30');
INSERT INTO `page1-1` VALUES ('2011', '11', '52688.0399', '29032.0638', '58792.8915', '140512.9952', '', '', '31');
INSERT INTO `page1-1` VALUES ('2011', '12', '50778.1824', '24184.4189', '52061.3935', '127023.9948', '', '', '32');
INSERT INTO `page1-1` VALUES ('2012', '1', '67452.0000', '27755.9989', '49381.9981', '144589.9970', '', '', '33');
INSERT INTO `page1-1` VALUES ('2012', '2', '65157.0003', '25064.9984', '44974.9978', '135196.9965', '', '', '34');
INSERT INTO `page1-1` VALUES ('2012', '3', '51895.9998', '35507.9970', '62303.6625', '149707.6593', '', '', '35');
INSERT INTO `page1-1` VALUES ('2012', '4', '59680.0026', '34401.9968', '68407.0042', '162489.0036', '', '', '36');
INSERT INTO `page1-1` VALUES ('2012', '5', '76293.0024', '36553.0013', '71262.9987', '184109.0024', '', '', '37');
INSERT INTO `page1-1` VALUES ('2012', '6', '91630.0031', '36862.0028', '63096.9939', '191588.9998', '', '', '38');
INSERT INTO `page1-1` VALUES ('2012', '7', '121355.0076', '35470.9997', '50525.9990', '207352.0063', '', '', '39');
INSERT INTO `page1-1` VALUES ('2012', '8', '104053.9946', '38849.0009', '56643.0022', '199545.9977', '', '', '40');
INSERT INTO `page1-1` VALUES ('2012', '9', '87243.9999', '38957.9976', '62945.9968', '189147.9943', '', '', '41');
INSERT INTO `page1-1` VALUES ('2012', '10', '75514.0044', '35562.0016', '63570.9984', '174647.0044', '', '', '42');
INSERT INTO `page1-1` VALUES ('2012', '11', '47608.0004', '30661.0043', '62245.0048', '140514.0095', '', '', '43');
INSERT INTO `page1-1` VALUES ('2012', '12', '13527.2000', '10924.0403', '26763.0404', '51214.2807', '', '', '44');
INSERT INTO `page1-1` VALUES ('2013', '1', '15955.4400', '9888.8000', '18707.3600', '44551.6000', '', '', '45');
INSERT INTO `page1-1` VALUES ('2013', '2', '14813.7600', '9452.2400', '17036.0800', '41302.0800', '', '', '46');
INSERT INTO `page1-1` VALUES ('2013', '3', '13567.9200', '9950.6400', '18263.4400', '41782.0000', '', '', '47');
INSERT INTO `page1-1` VALUES ('2013', '4', '12965.6000', '8086.0000', '17543.6000', '38595.2000', '', '', '48');
INSERT INTO `page1-1` VALUES ('2013', '5', '12363.7600', '8034.6400', '16605.0400', '37003.4400', '', '', '49');
INSERT INTO `page1-1` VALUES ('2013', '6', '15822.8800', '8388.8800', '16211.9200', '40423.6800', '', '', '50');
INSERT INTO `page1-1` VALUES ('2013', '7', '21269.0400', '9082.4000', '16352.4800', '46703.9200', '', '', '51');
INSERT INTO `page1-1` VALUES ('2013', '8', '18727.4400', '10274.1600', '18108.9600', '47110.5600', '', '', '52');
INSERT INTO `page1-1` VALUES ('2013', '9', '16470.5600', '8159.7600', '16271.9200', '40902.2400', '', '', '53');

could you please also change VARCHAR(255) in F_SUM and AVERAGE_12_MONTHS as well

Ok:

-- ----------------------------
-- Table structure for `page1-1`
-- ----------------------------
DROP TABLE IF EXISTS `page1-1`;
CREATE TABLE `page1-1` (
  `YEARS` int(11) DEFAULT NULL,
  `MONTHS` int(11) DEFAULT NULL,
  `F1` decimal(10,4) DEFAULT NULL,
  `F2` decimal(10,4) DEFAULT NULL,
  `F3` decimal(10,4) DEFAULT NULL,
  `F_SUM` decimal(10,4) DEFAULT NULL,
  `AVERAGE_12_MONTHS` decimal(10,4) DEFAULT NULL,
  `VAR` int(11)DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of page1-1
-- ----------------------------
INSERT INTO `page1-1` VALUES ('2009', '5', '92626.4000', '32796.5600', '59924.6400', '185347.6000', '', '', '1');
INSERT INTO `page1-1` VALUES ('2009', '6', '95008.6400', '31933.6000', '56977.8400', '183920.0800', '', '', '2');
INSERT INTO `page1-1` VALUES ('2009', '7', '111853.1200', '33091.9200', '53262.4000', '198207.4400', '', '', '3');
INSERT INTO `page1-1` VALUES ('2009', '8', '108379.9200', '32500.4800', '60452.6400', '201333.0400', '', '', '4');
INSERT INTO `page1-1` VALUES ('2009', '9', '102605.4400', '34639.3600', '58108.4000', '195353.2000', '', '', '5');
INSERT INTO `page1-1` VALUES ('2009', '10', '83442.5600', '31952.4000', '52181.6000', '167576.5600', '', '', '6');
INSERT INTO `page1-1` VALUES ('2009', '11', '55527.8400', '24864.0000', '47962.8000', '128354.6400', '', '', '7');
INSERT INTO `page1-1` VALUES ('2009', '12', '45067.2800', '21627.2800', '50793.6000', '117488.1600', '', '', '8');
INSERT INTO `page1-1` VALUES ('2010', '1', '50108.6386', '32070.3592', '88992.3954', '171171.3932', '', '', '9');
INSERT INTO `page1-1` VALUES ('2010', '2', '45229.0026', '32119.9970', '57848.0026', '135197.0022', '', '', '10');
INSERT INTO `page1-1` VALUES ('2010', '3', '51329.9947', '34104.9989', '64472.8217', '149907.8153', '', '', '11');
INSERT INTO `page1-1` VALUES ('2010', '4', '47097.9986', '38268.0023', '77124.0064', '162490.0073', '', '', '12');
INSERT INTO `page1-1` VALUES ('2010', '5', '43667.0032', '31968.9999', '108472.0064', '184108.0095', '', '', '13');
INSERT INTO `page1-1` VALUES ('2010', '6', '55458.9969', '32521.0012', '103608.0000', '191587.9981', '', '', '14');
INSERT INTO `page1-1` VALUES ('2010', '7', '99716.0032', '33510.9958', '74126.9942', '207353.9932', '', '', '15');
INSERT INTO `page1-1` VALUES ('2010', '8', '101051.9955', '31377.0024', '67117.9971', '199546.9950', '', '', '16');
INSERT INTO `page1-1` VALUES ('2010', '9', '72834.0026', '35291.0002', '81023.0042', '189148.0070', '', '', '17');
INSERT INTO `page1-1` VALUES ('2010', '10', '42791.0036', '44317.0016', '87538.9998', '174647.0050', '', '', '18');
INSERT INTO `page1-1` VALUES ('2010', '11', '41505.9969', '32237.0001', '66770.9952', '140513.9922', '', '', '19');
INSERT INTO `page1-1` VALUES ('2010', '12', '38599.0050', '27211.9987', '61212.9974', '127024.0011', '', '', '20');
INSERT INTO `page1-1` VALUES ('2011', '1', '55458.9976', '28852.9989', '60279.0048', '144591.0013', '', '', '21');
INSERT INTO `page1-1` VALUES ('2011', '2', '46786.0049', '29952.0022', '58459.0003', '135197.0074', '', '', '22');
INSERT INTO `page1-1` VALUES ('2011', '3', '52550.0057', '32447.0006', '64910.7606', '149907.7669', '', '', '23');
INSERT INTO `page1-1` VALUES ('2011', '4', '39288.9992', '41818.0032', '81383.0011', '162490.0035', '', '', '24');
INSERT INTO `page1-1` VALUES ('2011', '5', '84902.9996', '32890.0020', '66315.9978', '184108.9994', '', '', '25');
INSERT INTO `page1-1` VALUES ('2011', '6', '94883.7325', '35711.4508', '60993.8301', '191589.0134', '', '', '26');
INSERT INTO `page1-1` VALUES ('2011', '7', '113265.9990', '38767.0016', '55319.9899', '207352.9905', '', '', '27');
INSERT INTO `page1-1` VALUES ('2011', '8', '122653.8295', '28179.2513', '48713.9252', '199547.0060', '', '', '28');
INSERT INTO `page1-1` VALUES ('2011', '9', '118939.0022', '25380.0064', '44827.9981', '189147.0067', '', '', '29');
INSERT INTO `page1-1` VALUES ('2011', '10', '72671.0032', '35154.0034', '66821.9968', '174647.0034', '', '', '30');
INSERT INTO `page1-1` VALUES ('2011', '11', '52688.0399', '29032.0638', '58792.8915', '140512.9952', '', '', '31');
INSERT INTO `page1-1` VALUES ('2011', '12', '50778.1824', '24184.4189', '52061.3935', '127023.9948', '', '', '32');
INSERT INTO `page1-1` VALUES ('2012', '1', '67452.0000', '27755.9989', '49381.9981', '144589.9970', '', '', '33');
INSERT INTO `page1-1` VALUES ('2012', '2', '65157.0003', '25064.9984', '44974.9978', '135196.9965', '', '', '34');
INSERT INTO `page1-1` VALUES ('2012', '3', '51895.9998', '35507.9970', '62303.6625', '149707.6593', '', '', '35');
INSERT INTO `page1-1` VALUES ('2012', '4', '59680.0026', '34401.9968', '68407.0042', '162489.0036', '', '', '36');
INSERT INTO `page1-1` VALUES ('2012', '5', '76293.0024', '36553.0013', '71262.9987', '184109.0024', '', '', '37');
INSERT INTO `page1-1` VALUES ('2012', '6', '91630.0031', '36862.0028', '63096.9939', '191588.9998', '', '', '38');
INSERT INTO `page1-1` VALUES ('2012', '7', '121355.0076', '35470.9997', '50525.9990', '207352.0063', '', '', '39');
INSERT INTO `page1-1` VALUES ('2012', '8', '104053.9946', '38849.0009', '56643.0022', '199545.9977', '', '', '40');
INSERT INTO `page1-1` VALUES ('2012', '9', '87243.9999', '38957.9976', '62945.9968', '189147.9943', '', '', '41');
INSERT INTO `page1-1` VALUES ('2012', '10', '75514.0044', '35562.0016', '63570.9984', '174647.0044', '', '', '42');
INSERT INTO `page1-1` VALUES ('2012', '11', '47608.0004', '30661.0043', '62245.0048', '140514.0095', '', '', '43');
INSERT INTO `page1-1` VALUES ('2012', '12', '13527.2000', '10924.0403', '26763.0404', '51214.2807', '', '', '44');
INSERT INTO `page1-1` VALUES ('2013', '1', '15955.4400', '9888.8000', '18707.3600', '44551.6000', '', '', '45');
INSERT INTO `page1-1` VALUES ('2013', '2', '14813.7600', '9452.2400', '17036.0800', '41302.0800', '', '', '46');
INSERT INTO `page1-1` VALUES ('2013', '3', '13567.9200', '9950.6400', '18263.4400', '41782.0000', '', '', '47');
INSERT INTO `page1-1` VALUES ('2013', '4', '12965.6000', '8086.0000', '17543.6000', '38595.2000', '', '', '48');
INSERT INTO `page1-1` VALUES ('2013', '5', '12363.7600', '8034.6400', '16605.0400', '37003.4400', '', '', '49');
INSERT INTO `page1-1` VALUES ('2013', '6', '15822.8800', '8388.8800', '16211.9200', '40423.6800', '', '', '50');
INSERT INTO `page1-1` VALUES ('2013', '7', '21269.0400', '9082.4000', '16352.4800', '46703.9200', '', '', '51');
INSERT INTO `page1-1` VALUES ('2013', '8', '18727.4400', '10274.1600', '18108.9600', '47110.5600', '', '', '52');
INSERT INTO `page1-1` VALUES ('2013', '9', '16470.5600', '8159.7600', '16271.9200', '40902.2400', '', '', '53');

okay, you changed the datatype, but you forgot to change the values in the INSERT statements

SQL Error (1366): Incorrect decimal value: ‘’ for column ‘AVERAGE_12_MONTHS’ at row 1

you cannot insert strings into numeric columns

:slight_smile:

-- ----------------------------
-- Table structure for `page1-1`
-- ----------------------------
DROP TABLE IF EXISTS `page1-1`;
CREATE TABLE `page1-1` (
  `YEARS` int(11) DEFAULT NULL,
  `MONTHS` int(11) DEFAULT NULL,
  `F1` decimal(10,4) DEFAULT NULL,
  `F2` decimal(10,4) DEFAULT NULL,
  `F3` decimal(10,4) DEFAULT NULL,
  `F_SUM` decimal(10,4) DEFAULT NULL,
  `AVERAGE_12_MONTHS` decimal(10,4) DEFAULT NULL,
  `VAR` int(11) DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of page1-1
-- ----------------------------
INSERT INTO `page1-1` VALUES ('2009', '5', '92626.4000', '32796.5600', '59924.6400', '185347.6000', null, null, '1');
INSERT INTO `page1-1` VALUES ('2009', '6', '95008.6400', '31933.6000', '56977.8400', '183920.0800', null, null, '2');
INSERT INTO `page1-1` VALUES ('2009', '7', '111853.1200', '33091.9200', '53262.4000', '198207.4400', null, null, '3');
INSERT INTO `page1-1` VALUES ('2009', '8', '108379.9200', '32500.4800', '60452.6400', '201333.0400', null, null, '4');
INSERT INTO `page1-1` VALUES ('2009', '9', '102605.4400', '34639.3600', '58108.4000', '195353.2000', null, null, '5');
INSERT INTO `page1-1` VALUES ('2009', '10', '83442.5600', '31952.4000', '52181.6000', '167576.5600', null, null, '6');
INSERT INTO `page1-1` VALUES ('2009', '11', '55527.8400', '24864.0000', '47962.8000', '128354.6400', null, null, '7');
INSERT INTO `page1-1` VALUES ('2009', '12', '45067.2800', '21627.2800', '50793.6000', '117488.1600', null, null, '8');
INSERT INTO `page1-1` VALUES ('2010', '1', '50108.6386', '32070.3592', '88992.3954', '171171.3932', null, null, '9');
INSERT INTO `page1-1` VALUES ('2010', '2', '45229.0026', '32119.9970', '57848.0026', '135197.0022', null, null, '10');
INSERT INTO `page1-1` VALUES ('2010', '3', '51329.9947', '34104.9989', '64472.8217', '149907.8153', null, null, '11');
INSERT INTO `page1-1` VALUES ('2010', '4', '47097.9986', '38268.0023', '77124.0064', '162490.0073', null, null, '12');
INSERT INTO `page1-1` VALUES ('2010', '5', '43667.0032', '31968.9999', '108472.0064', '184108.0095', null, null, '13');
INSERT INTO `page1-1` VALUES ('2010', '6', '55458.9969', '32521.0012', '103608.0000', '191587.9981', null, null, '14');
INSERT INTO `page1-1` VALUES ('2010', '7', '99716.0032', '33510.9958', '74126.9942', '207353.9932', null, null, '15');
INSERT INTO `page1-1` VALUES ('2010', '8', '101051.9955', '31377.0024', '67117.9971', '199546.9950', null, null, '16');
INSERT INTO `page1-1` VALUES ('2010', '9', '72834.0026', '35291.0002', '81023.0042', '189148.0070', null, null, '17');
INSERT INTO `page1-1` VALUES ('2010', '10', '42791.0036', '44317.0016', '87538.9998', '174647.0050', null, null, '18');
INSERT INTO `page1-1` VALUES ('2010', '11', '41505.9969', '32237.0001', '66770.9952', '140513.9922', null, null, '19');
INSERT INTO `page1-1` VALUES ('2010', '12', '38599.0050', '27211.9987', '61212.9974', '127024.0011', null, null, '20');
INSERT INTO `page1-1` VALUES ('2011', '1', '55458.9976', '28852.9989', '60279.0048', '144591.0013', null, null, '21');
INSERT INTO `page1-1` VALUES ('2011', '2', '46786.0049', '29952.0022', '58459.0003', '135197.0074', null, null, '22');
INSERT INTO `page1-1` VALUES ('2011', '3', '52550.0057', '32447.0006', '64910.7606', '149907.7669', null, null, '23');
INSERT INTO `page1-1` VALUES ('2011', '4', '39288.9992', '41818.0032', '81383.0011', '162490.0035', null, null, '24');
INSERT INTO `page1-1` VALUES ('2011', '5', '84902.9996', '32890.0020', '66315.9978', '184108.9994', null, null, '25');
INSERT INTO `page1-1` VALUES ('2011', '6', '94883.7325', '35711.4508', '60993.8301', '191589.0134', null, null, '26');
INSERT INTO `page1-1` VALUES ('2011', '7', '113265.9990', '38767.0016', '55319.9899', '207352.9905', null, null, '27');
INSERT INTO `page1-1` VALUES ('2011', '8', '122653.8295', '28179.2513', '48713.9252', '199547.0060', null, null, '28');
INSERT INTO `page1-1` VALUES ('2011', '9', '118939.0022', '25380.0064', '44827.9981', '189147.0067', null, null, '29');
INSERT INTO `page1-1` VALUES ('2011', '10', '72671.0032', '35154.0034', '66821.9968', '174647.0034', null, null, '30');
INSERT INTO `page1-1` VALUES ('2011', '11', '52688.0399', '29032.0638', '58792.8915', '140512.9952', null, null, '31');
INSERT INTO `page1-1` VALUES ('2011', '12', '50778.1824', '24184.4189', '52061.3935', '127023.9948', null, null, '32');
INSERT INTO `page1-1` VALUES ('2012', '1', '67452.0000', '27755.9989', '49381.9981', '144589.9970', null, null, '33');
INSERT INTO `page1-1` VALUES ('2012', '2', '65157.0003', '25064.9984', '44974.9978', '135196.9965', null, null, '34');
INSERT INTO `page1-1` VALUES ('2012', '3', '51895.9998', '35507.9970', '62303.6625', '149707.6593', null, null, '35');
INSERT INTO `page1-1` VALUES ('2012', '4', '59680.0026', '34401.9968', '68407.0042', '162489.0036', null, null, '36');
INSERT INTO `page1-1` VALUES ('2012', '5', '76293.0024', '36553.0013', '71262.9987', '184109.0024', null, null, '37');
INSERT INTO `page1-1` VALUES ('2012', '6', '91630.0031', '36862.0028', '63096.9939', '191588.9998', null, null, '38');
INSERT INTO `page1-1` VALUES ('2012', '7', '121355.0076', '35470.9997', '50525.9990', '207352.0063', null, null, '39');
INSERT INTO `page1-1` VALUES ('2012', '8', '104053.9946', '38849.0009', '56643.0022', '199545.9977', null, null, '40');
INSERT INTO `page1-1` VALUES ('2012', '9', '87243.9999', '38957.9976', '62945.9968', '189147.9943', null, null, '41');
INSERT INTO `page1-1` VALUES ('2012', '10', '75514.0044', '35562.0016', '63570.9984', '174647.0044', null, null, '42');
INSERT INTO `page1-1` VALUES ('2012', '11', '47608.0004', '30661.0043', '62245.0048', '140514.0095', null, null, '43');
INSERT INTO `page1-1` VALUES ('2012', '12', '13527.2000', '10924.0403', '26763.0404', '51214.2807', null, null, '44');
INSERT INTO `page1-1` VALUES ('2013', '1', '15955.4400', '9888.8000', '18707.3600', '44551.6000', null, null, '45');
INSERT INTO `page1-1` VALUES ('2013', '2', '14813.7600', '9452.2400', '17036.0800', '41302.0800', null, null, '46');
INSERT INTO `page1-1` VALUES ('2013', '3', '13567.9200', '9950.6400', '18263.4400', '41782.0000', null, null, '47');
INSERT INTO `page1-1` VALUES ('2013', '4', '12965.6000', '8086.0000', '17543.6000', '38595.2000', null, null, '48');
INSERT INTO `page1-1` VALUES ('2013', '5', '12363.7600', '8034.6400', '16605.0400', '37003.4400', null, null, '49');
INSERT INTO `page1-1` VALUES ('2013', '6', '15822.8800', '8388.8800', '16211.9200', '40423.6800', null, null, '50');
INSERT INTO `page1-1` VALUES ('2013', '7', '21269.0400', '9082.4000', '16352.4800', '46703.9200', null, null, '51');
INSERT INTO `page1-1` VALUES ('2013', '8', '18727.4400', '10274.1600', '18108.9600', '47110.5600', null, null, '52');
INSERT INTO `page1-1` VALUES ('2013', '9', '16470.5600', '8159.7600', '16271.9200', '40902.2400', null, null, '53');

okay, that works, but you do realize that all the other strings in your INSERT statements are intended for numeric columns, right? they shouldn’t have quotes either, but luckily (perhaps not the adverb many people would use), mysql is quite forgiving

anyhow, now we are at the point of calculating a moving average

what do you think would be the best way to calculate which rows should go into the calculation?

in other words, how do you define the range of rows for the sum?

HINT: DO NOT USE THE AUTO_INCREMENT

Right … :slight_smile:

Okay :rolleyes:

anyhow, now we are at the point of calculating a moving average

what do you think would be the best way to calculate which rows should go into the calculation?

in other words, how do you define the range of rows for the sum?

this is what I don’t understand… how to iterate through the rows back in a mysql table ? :frowning:

here’s a hint – within the last 12 months

WHERE some_date_diff BETWEEN 0 AND 11

thanks, you mean this?

mysql> SELECT
	AVG(DISTINCT F_SUM)
FROM
	`page1-1`
WHERE
	MONTHS BETWEEN 0
AND 11;
+---------------------+
| AVG(DISTINCT F_SUM) |
+---------------------+
| 149022.83596939     |
+---------------------+
1 row in set

nope :slight_smile:

let me try to explain it this way…

pick any row in your table… go ahead, pick one in your mind, a specific year and month

okay, now let’s say i’m trying to calculate the 12 month moving average for year 2010 month 09… how do i know whether the row you are thinking of should be included in the 12 months?

Okey, for calculate the 12 month moving average for year 2010 month 09 I need iterate the rows …

2009	10
2009	11
2009	12
2010	01
2010	02
2010	03
2010	04
2010	05
2010	06
2010	07
2010	08
2010	09

correct

now, think about how you would compare the year and month of those rows in a WHERE clause

hint: it’s a self-join

Sorry I don’t understand …

you need to compare a given row (let’s say 2010 09) with all the other rows in the table, and keep only those rows where the year and month is within the preceeding 12 months of 2010 09, and use those 12 rows to compute the average for 2010 09

to compare rows like that, you need a self-join – google it

and let me give you some advice: if you cannot write the self-join, then perhaps you should consider pulling the entire table into your application (php or whatever) and doing that 12 month row comparison there

and if you can’t do that either, then perhaps you should hire someone (hint: not me)

thank you, I tried this solution and worked:

CREATE TABLE foo SELECT
	ID,
	F1,
	F2,
	F3,
	F_SUM,
	@sum1 := @sum1 + F_SUM AS sum1,
	var
FROM
	`page1-1`
JOIN (SELECT @sum1 := 0) init;

UPDATE `page1-1` t1
JOIN (
	SELECT
		b.ID,
		b.f1,
		b.f2,
		b.f3,
		ROUND(b.F_SUM, 2) AS FSUM,
		ROUND((b.sum1 - a.sum1) / 12, 2) AS MA,
		b.var
	FROM
		foo a
	JOIN foo b ON a.ID = b.ID - 12
) AS t2 ON t1.ID = t2.ID
SET t1.AVERAGE_12_MONTHS = t2.MA;

that’s nice

i notice you used a.ID = b.ID - 12

this is problematic unless (1) your id values are synchronized to year and month, and (2) no year/month is missing

also, please double check your updated values for the first 11 rows in the table