I have this query from Uploads table:

select
    Costumer as Customer,
    max(Week) as 'Max Week',
    count(distinct(POS)) as 'Total POS'
from Uploads
where year = 2022
group by Costumer;

and returns this:

This table has an additional column named Inventory and I want to know the SUM(Inventory) but with the weeks filtered before. For example:

CodePudding user response：

You can solve this issue by using Row_Number like this

SELECT t.[Total POS],
       t.customer,
       t.week MaxWeek,
       t.SumInventoryPerWeek SumForMaxWeek
       FROM (
select
    Costumer as Customer,
    Week as week,
    count(distinct(POS)) as 'Total POS',
    SUM(Inventory) SumInventoryPerWeek,
    ROW_NUMBER() OVER(PARTITION BY Costumer ORDER BY Week DESC) rw
from Uploads
where year = 2022
group by Costumer,Week
) t
WHERE t.rw=1

CodePudding user response：

SELECT
Costumer as Customer,
max(Week) as 'Max Week',
COUNT(DISTINCT(POS)) as 'Total POS',
(SELECT Inventory from Uploads uploads_tbl WHERE Uploads.Costumer = uploads_tbl.Costumer and uploads_tbl.Week = Uploads.MAX(Week)) as Inventory 
from Uploads
where year = 2022
group by Costumer;