Named Window functions in MySQL

For queries where numerous OVER clauses would ordinarily define the same window, a WINDOW clause is helpful. Instead, you can create a single definition for the window, assign it a name, and then make reference to the name in the OVER clauses.

Syntax:
WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] …

Window_name is the window name for each definition, and window_spec is the same type of window specification as that specified in an OVER clause’s parenthesis.

Get the dataset from here.

SELECT DISTINCT

ProductCategory,

Manufacturer,

SUM( PricePerItem * UnitSales ) OVER ( PARTITION BY Manufacturer, ProductCategory ) AS Manufacturer_TotalSales,

AVG( PricePerItem * UnitSales ) OVER ( PARTITION BY Manufacturer, ProductCategory ) AS Manufacturer_AVGSales,

MAX( UnitSales ) OVER ( PARTITION BY Manufacturer, ProductCategory ) AS HighestSalesPerCategry,

MIN( UnitSales ) OVER ( PARTITION BY Manufacturer, ProductCategory ) AS LowestSalesPerCategry 

FROM

tbProducts 

ORDER BY

Manufacturer;

SELECT DISTINCT

ProductCategory,

Manufacturer,

SUM( PricePerItem * UnitSales ) OVER PartionClause AS Manufacturer_TotalSales,

AVG( PricePerItem * UnitSales ) OVER PartionClause AS Manufacturer_AVGSales,

MAX( UnitSales ) OVER PartionClause AS HighestSalesPerCategry,

MIN( UnitSales ) OVER PartionClause AS LowestSalesPerCategry 

FROM

tbProducts 

WINDOW PartionClause AS ( PARTITION BY Manufacturer, ProductCategory )

ORDER BY

Manufacturer;