Query to sum the top "n" records-CodePudding

I would like to sum the forecast qty column for only the first "n" date records. For example for each item number the earliest(or first) three date records

Current Query:

SELECT gen.Item ,
        gen.DemandDate ,
        gen.ForecastQty 
FROM reports.v_special_demand_800 gen 
ORDER BY gen.Item, gen.DemandDate ASC

Table

Desired Result

CodePudding user response：

Assign a row number to each record, partitioning by Item (this will start a new row counter for each Item) and ordering by DemandDate. Then sum the records where the row number is <= N, grouping by Item:

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY DemandDate) as RowNum
    FROM reports.v_special_demand_800
)
SELECT Item, SUM(ForecastQty)
FROM CTE
WHERE RowNum <= 3
GROUP BY Item