Complicated update query

Transact-SQL
1

I have a rather complicated update query with which I need help please.
I know this is a SQL forum and I use MYSQL. I am not getting any responses from the MySQL forum, so in desperation I am trying here. So please bear with me.

Background : It is about winegrape loads delivered a wine cellar. I need to calculate the weighted average sugar per name,variety and class for each load per day.
In Excel I do the following. I multiply the tonnes with sugar for each load to get a value. I then filter per name,variety and class and sort the sugar in desending. Finally for each load I calculate the weighted average by deviding the sum of tonnes by the sum of the tonnesXsugar values of all the values including and above the current row.
However I get the wrong answer. The desired answer is in the last column in the attached file. I have filtered for Name = ‘BOESMAN GILLIOMEE’,date = ‘'2021-02-24’ and variety = 'CHB'

I used the following query :

UPDATE penalisasie AS r 
         JOIN 
           ( SELECT   date,name,variety,class,sugar, (SUM(TonnesxSugar)/SUM(Tonnes)) AS WAVG_DAY 
              FROM     penalisasie 
                   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.Weighted_average = grp. WAVG_DAY;



Any help would be much appreciated.

PS. I use double data type for the numeric values. Sorry I am not inluding the table definition – it is not in English and might be difficult to understand.

ExcelExample
ExcelExample1076×507 114 KB

EDIT

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
2

Welcome

Help us help you by providing sample data not as an image but as ddl and dml

Create table #sample

Insert into #sample
3

Thank you for replying yosiasz. I have edited my question. See after EDIT. Regards

4

Windowed functions can be used to get the values so far.
SQL Server allows CTEs to be updated.

WITH AvgWeights
AS
(
	SELECT Name, Variety, Class, Date, Sugar, Tonnes, TonnesXSugar_Load, WeighAve_SugarPerDay
		,AVG(TonnesXSugar_Load)
			OVER (PARTITION BY Name, Variety, Class, Date 
					ORDER BY Sugar DESC, Tonnes DESC)
		/
			AVG(Tonnes)
				OVER (PARTITION BY Name, Variety, Class, Date 
						ORDER BY Sugar DESC, Tonnes DESC) AS AvgWeight
	FROM penalisasiet
)
UPDATE AvgWeights
SET WeighAve_SugarPerDay = AvgWeight;
5

Thank you Ifor.

Your query does not work work for MySQL - I know I'm on the wrong forum, I have tried MySQL forum but in desperation I'm trying here.

So I've come up with the following query which in most cases equal the desired results :

update penalisasiet
set WeighAve_SugarPerDay = (select * from (select SUM(TonnesXSugar_Load)/SUM(Tonnes) from penalisasiet t2
where penalisasiet.date = t2.date AND
penalisasiet.variety = t2.variety AND
penalisasiet.name = t2.name AND
penalisasiet.class = t2.class AND
penalisasiet.sugar <= t2.sugar )tblTemp);

BTW. I have changed all the double to decimal(20,10) data type.

My query works fine except when the sugar are the same, which does make sense (since penalisasiet.sugar <= t2.sugar). See in my data sugar = 21. I have tried with reordering an auto increment primary key column and using penalisasiet.ID < t2.ID. However this approach does not work.

Regards

6

I do not know much about MySQL but if you have an Id PK I think, based on your previous queries, the following may be worth trying:

UPDATE penalisasiet AS P
	JOIN
	(
		SELECT Id
			,SUM(TonnesXSugar_Load)
				OVER (PARTITION BY Name, Variety, Class, Date 
						ORDER BY Sugar DESC, Tonnes DESC)
			/
				SUM(Tonnes)
					OVER (PARTITION BY Name, Variety, Class, Date 
							ORDER BY Sugar DESC, Tonnes DESC) AS AvgWeight
		FROM penalisasiet
	) AS D
	ON P.Id = D.Id
SET WeighAve_SugarPerDay = D.AvgWeight;

ps I have just googled MySQL and windowed functions. They were not available until version 8.