[SOLVED] SUM from a preview query (mysql)

Issue

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:

Customer Max Week Total POS
Customer A 3 65
Customer B 5 27
Customer C 3 33

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

Customer Max Week Total POS Inventory
Customer A 3 65 456
Customer B 5 27 123
Customer C 3 33 2345

Solution

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

Answered By – Saeed Esmaeelinejad

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.