MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table stats
(
    id bigint unsigned auto_increment primary key,
    product_id bigint unsigned not null,
    size varchar(255) not null,
    orders bigint unsigned default '0' not null,
    sales bigint unsigned default '0' not null,
    stocks bigint unsigned default '0' not null,
    warehouse_id bigint unsigned null,
    date date not null
);

INSERT INTO stats (product_id,size,orders,sales,stocks,warehouse_id,date) VALUES (85,'45',1,1,10,1,'2023-09-01');
INSERT INTO stats (product_id,size,orders,sales,stocks,warehouse_id,date) VALUES (85,'45',1,1,8,1,'2023-09-02');
INSERT INTO stats (product_id,size,orders,sales,stocks,warehouse_id,date) VALUES (85,'45',1,1,6,1,'2023-09-03');
-- исходный запрос автора
select
    product_id,
    size,
    sum(orders) as orders,
    sum(stocks) as stocks,
    warehouse_id
from stats
where date(`date`) >= '2023-09-01' and date(`date`) <= '2023-09-04'
group by product_id, warehouse_id, size;


product_id
size
orders
stocks
warehouse_id


85
45
3
24
1


-- откорректированный запрос
select DISTINCT
    product_id,
    size,
    sum(orders) OVER (PARTITION BY product_id, warehouse_id, size) as orders,
    FIRST_VALUE(stocks) OVER (PARTITION BY product_id, warehouse_id, size ORDER BY `date` DESC) as stocks,
    warehouse_id
from stats
where date(`date`) >= '2023-09-01' and date(`date`) <= '2023-09-04'
/* group by product_id, warehouse_id, size */;


product_id
size
orders
stocks
warehouse_id


85
45
3
6
1