SQLTeam.com | Weblogs | Forums

Complicated update query

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. 

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

Welcome

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

Create table #sample

Insert into #sample

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

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;

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

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.