Re: Complicated update query
Thank you for replying Peter.
1. When I used decimal(10,2) I get a warning truncated data. I will use decimal in future with more digits after the separator.
2. I did not realize I have to use MySQL date format for using date logic. I will do so from now on.
3. I have updated my question (see below).
4. I have included a column 'Desired' in my insert statement below to see the desired result.
5. I am using 8.0.23
My create statement is as follows:
CREATE TABLE `penalisasiet` (
`Name` text,
`Variety` text,
`Class` text,
`Date` date DEFAULT NULL,
`Sugar` double DEFAULT NULL,
`Tonnes` double DEFAULT NULL,
`TonnesXSugar_Load` double DEFAULT NULL,
`WeighAve_SugarPerDay` double DEFAULT NULL,
`Desired` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Please note the last field (Desired) is not actually part of the table - I have included it because that is my desired result.
My insert statement is as follows:
INSERT INTO
penalisasiet(Name,Variety,Class,Date,Sugar,Tonnes,TonnesXSugar_Load,WeighAve_SugarPerDay,Desired)
VALUES
('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','22','6.32','139.04','22','22'),
('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','21.9','6.86','150.234','21.9','21.94795144'),
('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','21.6','4.9','105.84','21.6','21.85365044'),
('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','21.2','6.37','135.044','21.2','21.68335378'),
('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','21.1','5.95','125.545','21.1','21.56917763'),
('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','20.8','5.91','122.928','20.8','21.44398237'),
('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','20.5','5.99','122.795','20.5','21.31030733'),
('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','21.2','5.83','123.596','21.2','21.2'),
('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','21','6.3','132.3','21','21.09612531'),
('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','21','5.72','120.12','21','21.06532213'),
('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','20.1','5.43','109.143','20.1','20.84016323'),
('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','19.9','6.11','121.589','19.9','20.64470908'),
('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','19.2','6.09','116.928','19.2','20.39673055');
What I want to do is update the field WeighAve_SugarPerDay by groupng Name,Variety,Class,Date. I then want for each row determine the weighted average for sugar based on the group. I calculate the weighted average by sum(TonnesXSugar_Load)/sum(Tonnes). For this last calculation I only want to include rows where the sugar >= the current row (i.e. including the current row)
I have tried using the following:
UPDATE penalisasiet AS r
JOIN
( SELECT date,name,variety,class,sugar, (SUM(TonnesXSugar_Load)/SUM(Tonnes)) AS WEIGHAVE
FROM penalisasiet
GROUP BY date,name,variety,class,sugar ) AS grp
ON
grp.date = r.date AND
grp.name = r.name AND
grp.variety = r.variety AND
grp.class = r.class AND
grp.sugar >= r.sugar
SET r.WeighAve_SugarPerDay = grp.WEIGHAVE ;
However I do not get the desired result as per the insert statement above. I think it has to do with using sugar in the group?
Thank you in anticipation your help.
Regards