Hi Tevin,
Use GROUP BY with AVG function.
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 StockCode,AVG(PartWeight) PartWeightAVG,AVG(SprueWeight) SprueWeightAVG,AVG(BomWeight) BomWeightAVG
FROM @tblComponent
GROUP BY StockCode
Output
| StockCode |
PartWeightAVG |
SprueWeightAVG |
BomWeightAVG |
| 000-256227-010 |
0.3290000000 |
0.04000000000 |
0.349000 |
| 000-256227-020 |
0.3300000000 |
0.04000000000 |
0.349000 |
| 000-256480-000 |
0.4800000000 |
0.00000000000 |
0.485000 |
| 000-256803-010 |
0.0790000000 |
0.00600000000 |
0.084000 |
| 000-256803-020 |
0.0635000000 |
0.01900000000 |
0.084000 |
| 000-256916-010 |
0.1160000000 |
0.09000000000 |
0.161300 |
| 000-256955-010 |
0.1030000000 |
0.04900000000 |
0.129000 |
| 000-256955-020 |
0.1030000000 |
0.04900000000 |
0.129000 |
| 000-256957-010 |
0.0500000000 |
0.03200000000 |
0.065000 |
