Query to select average of columns in SQL Server

Tevin
 
on Jun 03, 2021 07:45 AM
615 Views

Please assist me in writing a sql query for the following instructions:

The query must just return 1 row showing the stock code and the averages of records for that stock code from the partweight, sprueweight and bomweight.

Below is my sql table fields.

USE [Quality]
GO

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

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 04, 2021 02:11 AM

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