# Calculate and display last 10 records average in SQL Server

on Jun 08, 2021 11:03 PM
698 Views

I need to write a query that takes the last 10 recordings per component, get the average of those 10 and then display as 1 row.

Table:

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

Below is my query but it is not correct because it returns multiple values.

```SELECT  Top 10 cwc.StockCode,i.LongDesc AS 'Description', cwc.IsOutOfSpec,cwc.CapturedDateTime,
(SELECT  TOP 10 AVG(PartWeightGram) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode ) AS  'Part Weight Average',
(SELECT  TOP 10 AVG([SprueWeightGram]) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode  ) AS 'Sprue Weight Average',
(SELECT  TOP 10 AVG(c.TolerancePercentage) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode   ) AS 'Tolerance Average',
(SELECT  TOP 10 AVG([BomWeightKG]) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode   ) AS 'Bom Weight Average',
(SELECT  TOP 10 AVG((c.PartWeightGram + (c.SprueWeightGram / 2)) - (c.BomWeightKG * 1000)) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode   ) AS 'Variance To Syspro Average',
(SELECT  TOP 10 AVG((((((c.PartWeightGram + c.SprueWeightGram  / 2))) - (c.BomWeightKG * 1000)) / (c.BomWeightKG * 1000))  * 100) FROM tblComponentWeightCheck c WHERE c.StockCode = '000-256966-020' GROUP BY c.StockCode   ) AS 'Variance To Syspro & Average'
FROM tblComponentWeightCheck cwc
LEFT JOIN [Mercury].[EncoreCompanyA].dbo.InvMaster i ON  cwc.StockCode = i.StockCode
WHERE cwc.StockCode = '000-256966-020'
GROUP BY cwc.StockCode, i.LongDesc, cwc.IsOutOfSpec,cwc.CapturedDateTime
```

on Jun 09, 2021 11:31 PM

```SELECT MAX(CONVERT(Date, CapturedDateTime)) AS 'DateCaptured'
,StockCode
, LongDesc
,AVG(PartWeightGram) AS 'Part Weight Average'
,AVG(SprueWeightGram) AS 'Sprue Weight Average'
,AVG(TolerancePercentage) AS 'Tolerance Average'
,AVG(BomWeightKG * 1000) AS 'Bom Weight Average'
,AVG(PartWeightGram + ( SprueWeightGram / 2) - (BomWeightKG * 1000)) AS 'Variance To Syspro Average'
,AVG((((((PartWeightGram + SprueWeightGram  / 2))) - (BomWeightKG * 1000)) / (BomWeightKG * 1000))  * 100) AS 'VarianceToSysproPct'
,Case
WHEN ABS(AVG((((((PartWeightGram + SprueWeightGram  / 2))) - (BomWeightKG * 1000)) / (BomWeightKG * 1000))  * 100 )) > 5 Then 'True'
WHEN ABS(AVG((((((PartWeightGram + SprueWeightGram  / 2))) - (BomWeightKG * 1000)) / (BomWeightKG * 1000))  * 100 )) < 5 Then 'False'
Else 'False'
End AS 'Out Of Spec Average'
FROM
(
SELECT TOP 10 c.CapturedDateTime, c.StockCode,i.LongDesc
,(c.PartWeightGram)
,(c.SprueWeightGram)
,(c.TolerancePercentage)
,(c.BomWeightKG)
,(c.IsOutOfSpec)
FROM tblComponentWeightCheck c
LEFT JOIN [Mercury].[EncoreCompanyA].dbo.InvMaster i ON  c.StockCode = i.StockCode
WHERE c.StockCode='000-256576-020' And
Deleted = 'False'
AND CONVERT(Date, CapturedDateTime) Between '2021-05-01' AND '2021-06-01'
ORDER BY CapturedDateTime DESC
) a
GROUP BY StockCode, LongDesc```