Calculate average of last 10 record from each group in SQL Server

on Jun 06, 2021 11:44 PM

I would like to return record for the average part weight of the last 10 records for a particular stock code from the table below:

USE [Quality]
SELECT [ComponentWeightCheckID]
  FROM [dbo].[tblComponentWeightCheck]

This is my example but it returns multiple records.

SELECT TOP 10  StockCode , 
AVG(distinct s.PartWeightGram) AS 'PartWeightAVG' , 
AVG( distinct s.SprueWeightGram) AS 'SprueWeightAVG' , 
AVG( distinct s.BomWeightKG) AS 'BomWeightAVG' 
FROM tblComponentWeightCheck s
WHERE  StockCode = '000-256966-020' 
GROUP BY CapturedDateTime, StockCode


Download FREE API for Word, Excel and PDF in ASP.Net: Download
on Jun 07, 2021 07:18 AM

Hi Tevin,

Use sub query.

Refer below query.


DECLARE @tblComponent AS TABLE
    CapturedDateTime DATETIME,
    StockCode VARCHAR(20),
    LongDesc VARCHAR(50),
    PartWeight DECIMAL(18,11),
    SprueWeight DECIMAL(18,11),
    BomWeight DECIMAL(18,6),
    TolerancePercentage DECIMAL(18,2),
    VarianceToSysproPct DECIMAL(18,2),
    IsOutOfSpec BIT,
    VarianceToSyspro INT
INSERT INTO @tblComponent VALUES('2021-05-01 03:09:01','000-256227-010','GUIDE BRACKET LH',0.329,0.040,0.3490,5.0,0.00,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:10:44','000-256227-020','GUIDE BRACKET RH',0.330,0.040,0.3490,5.0,0.29,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:13:49','000-256955-010','REFL B TURN RCL LH',0.103,0.049,0.1290,5.0,-1.16,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:15:19','000-256955-020','REFL B TURN RCL RH',0.103,0.049,0.1290,5.0,-1.16,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:18:56','000-256480-000','GRILL RADIATOR',0.480,0.000,0.4850,5.0,-1.03,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:21:11','000-256803-010','GRAY ZONE CVR LH',0.079,0.006,0.0840,5.0,-2.38,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:22:45','000-256803-020','GRAY ZONE CVR RH',0.077,0.006,0.0840,5.0,-4.76,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:24:36','000-256957-010','INNER LENS',0.050,0.032,0.0650,5.0,0.76,0,0)
INSERT INTO @tblComponent VALUES('2021-05-01 03:26:14','000-256803-020','GRAY ZONE CVR RH',0.050,0.032,0.0840,5.0,-21.43,1,0)
INSERT INTO @tblComponent VALUES('2021-05-01 04:35:20','000-256916-010','EXT B LLEDHL LH',0.116,0.090,0.1613,5.0,0.19,0,0)
SELECT tc1.CapturedDateTime,tc1.StockCode,
(SELECT  TOP 10 AVG(PartWeight) FROM @tblComponent tc2 WHERE tc1.StockCode = tc2.StockCode) PartWeightAVG,
(SELECT  TOP 10 AVG(SprueWeight) FROM @tblComponent tc3 WHERE tc1.StockCode = tc3.StockCode) SprueWeightAVG,
(SELECT  TOP 10 AVG(BomWeight) FROM @tblComponent tc4 WHERE tc1.StockCode = tc4.StockCode) BomWeightAVG
FROM @tblComponent tc1
GROUP BY tc1.CapturedDateTime,tc1.StockCode