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

on Jun 06, 2021 11:44 PM
784 Views

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]
GO

SELECT [ComponentWeightCheckID]
,[EmpID]
,[StockCode]
,[Process]
,[PartWeightGram]
,[SprueWeightGram]
,[BomWeightKG]
,[TolerancePercentage]
,[AssetID]
,[CapturedDateTime]
,[Hostname]
,[IsOutOfSpec]
,[Tool]
,[Deleted]
FROM [dbo].[tblComponentWeightCheck]
GO```

This is my example but it returns multiple records.

```SELECT TOP 10  StockCode ,
CapturedDateTime,
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```

on Jun 07, 2021 07:18 AM
6 hours ago

Hi Tevin,

Use sub query.

Refer below query.

SQL

```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```

Output

 CapturedDateTime StockCode PartWeightAVG SprueWeightAVG BomWeightAVG 2021-05-01 03:09:01.000 000-256227-010 0.329 0.04 0.349 2021-05-01 03:10:44.000 000-256227-020 0.33 0.04 0.349 2021-05-01 03:13:49.000 000-256955-010 0.103 0.049 0.129 2021-05-01 03:15:19.000 000-256955-020 0.103 0.049 0.129 2021-05-01 03:18:56.000 000-256480-000 0.48 0 0.485 2021-05-01 03:21:11.000 000-256803-010 0.079 0.006 0.084 2021-05-01 03:22:45.000 000-256803-020 0.0635 0.019 0.084 2021-05-01 03:24:36.000 000-256957-010 0.05 0.032 0.065 2021-05-01 03:26:14.000 000-256803-020 0.0635 0.019 0.084 2021-05-01 04:35:20.000 000-256916-010 0.116 0.09 0.1613